Oracle7 Server Concepts Manual | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
George Dewey: at the battle of Manila Bay
This chapter discusses database triggers; that is, procedures that are stored in the database and implicitly executed ("fired") when a table is modified. This chapter includes:
If you are using Trusted Oracle, see the Trusted Oracle7 Server Administrator's Guide.
Triggers are similar to stored procedures, discussed in Chapter 14, "Procedures and Packages". A trigger can include SQL and PL/SQL statements to execute as a unit and can invoke stored procedures. However, procedures and triggers differ in the way that they are invoked. While a procedure is explicitly executed by a user, application, or trigger, one or more triggers are implicitly fired (executed) by Oracle when a triggering INSERT, UPDATE, or DELETE statement is issued, no matter which user is connected or which application is being used.
For example, Figure 15 - 1 shows a database application with some SQL statements that implicitly fire several triggers stored in the database.
Figure 15 - 1. Triggers
Notice that triggers are stored in the database separately from their associated tables.
Triggers can be defined only on tables, not on views. However, triggers on the base table(s) of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against a view.
In addition, triggers are commonly used to
Figure 15 - 2. Cascading Triggers
While triggers are useful for customizing a database, you should only use triggers when necessary. The excessive use of triggers can result in complex interdependences, which may be difficult to maintain in a large application.
Database triggers are defined on a table, stored in the associated database, and executed as a result of an INSERT, UPDATE, or DELETE statement being issued against a table, no matter which user or application issues the statement.
A declarative integrity constraint is a statement about the database that is never false while the constraint is enabled. A constraint applies to existing data in the table and any statement that manipulates the table.
Triggers constrain what transactions can do. A trigger does not apply to data loaded before the definition of the trigger. Therefore, it does not guarantee all data in a table conforms to its rules.
A trigger enforces transitional constraints; that is, a trigger only enforces a constraint at the time that the data changes. Therefore, a constraint such as "make sure that the delivery date is at least seven days from today" should be enforced by a trigger, not a declarative integrity constraint.
In evaluating triggers that contain SQL functions that have NLS parameters as arguments (for example, TO_CHAR, TO_DATE, and TO_NUMBER), the default values for these parameters are taken from the NLS parameters currently in effect for the session. You can override the default values by specifying NLS parameters explicitly in such functions when you create a trigger.
For more information about declarative integrity constraints, see Chapter 7, "Data Integrity".
Figure 15 - 3. The REORDER Trigger
For example, in Figure 15 - 3, the triggering statement is
. . . UPDATE OF parts_on_hand ON inventory . . .
which means that when the PARTS_ON_HAND column of a row in the INVENTORY table is updated, fire the trigger. Note that when the triggering event is an UPDATE statement, you can include a column list to identify which columns must be updated to fire the trigger. Because INSERT and DELETE statements affect entire rows of information, a column list cannot be specified for these options.
A triggering event can specify multiple DML statements, as in
. . . INSERT OR UPDATE OR DELETE OF inventory . . .
which means that when an INSERT, UPDATE, or DELETE statement is issued against the INVENTORY table, fire the trigger. When multiple types of DML statements can fire a trigger, conditional predicates can be used to detect the type of triggering statement. Therefore, a single trigger can be created that executes different code based on the type of statement that fired the trigger.
A trigger restriction is an option available for triggers that are fired for each row. Its function is to control the execution of a trigger conditionally. You specify a trigger restriction using a WHEN clause. For example, the REORDER trigger in Figure 15 - 3 has a trigger restriction. The trigger is fired by an UPDATE statement affecting the PARTS_ON_HAND column of the INVENTORY table, but the trigger action only fires if the following expression is TRUE:
new.parts_on_hand < new.reorder_point
Similar to stored procedures, a trigger action can contain SQL and PL/SQL statements, define PL/SQL language constructs (variables, constants, cursors, exceptions, and so on), and call stored procedures. Additionally, for row trigger, the statements in a trigger action have access to column values (new and old) of the current row being processed by the trigger. Two correlation names provide access to the old and new values for each column.
Row Triggers A row trigger is fired each time the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If a triggering statement affects no rows, a row trigger is not executed at all.
Row triggers are useful if the code in the trigger action depends on data provided by the triggering statement or rows that are affected. For example, Figure 15 - 3 illustrates a row trigger that uses the values of each row affected by the triggering statement.
Statement Triggers A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects (even if no rows are affected). For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once, regardless of how many rows are deleted from the table.
Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected. For example, if a trigger makes a complex security check on the current time or user, or if a trigger generates a single audit record based on the type of triggering statement, a statement trigger is used.
BEFORE Triggers BEFORE triggers execute the trigger action before the triggering statement. This type of trigger is commonly used in the following situations:
You can create as many triggers of the preceding different types as you need for each type of DML statement (INSERT, UPDATE, or DELETE). For example, suppose you have a table, SAL, and you want to know when the table is being accessed and the types of queries being issued. Figure 15 - 4 contains a sample package and trigger that tracks this information by hour and type of action (for example, UPDATE, DELETE, or INSERT) on table SAL. A global session variable, STAT.ROWCNT, is initialized to zero by a BEFORE statement trigger, then it is increased each time the row trigger is executed, and finally the statistical information is saved in the table STAT_TAB by the AFTER statement trigger.
DROP TABLE stat_tab;
CREATE TABLE stat_tab(utype CHAR(8),
rowcnt INTEGER, uhour INTEGER);
CREATE OR REPLACE PACKAGE stat IS
rowcnt INTEGER;
END;
/
CREATE TRIGGER bt BEFORE UPDATE OR DELETE OR INSERT ON sal
BEGIN
stat.rowcnt := 0;
END;
/
CREATE TRIGGER rt BEFORE UPDATE OR DELETE OR INSERT ON sal
FOR EACH ROW BEGIN
stat.rowcnt := stat.rowcnt + 1;
END;
/
CREATE TRIGGER at AFTER UPDATE OR DELETE OR INSERT ON sal
DECLARE
typ CHAR(8);
hour NUMBER;
BEGIN
IF updating
THEN typ := 'update'; END IF;
IF deleting THEN typ := 'delete'; END IF;
IF inserting THEN typ := 'insert'; END IF;
hour := TRUNC((SYSDATE - TRUNC(SYSDATE)) * 24);
UPDATE stat_tab
SET rowcnt = rowcnt + stat.rowcnt
WHERE utype = typ
AND uhour = hour;
IF SQL%ROWCOUNT = 0 THEN
INSERT INTO stat_tab VALUES (typ, stat.rowcnt, hour);
END IF;
EXCEPTION
WHEN dup_val_on_index THEN
UPDATE stat_tab
SET rowcnt = rowcnt + stat.rowcnt
WHERE utype = typ
AND uhour = hour;
END;
/
Figure 15 - 4. Sample Package and Trigger for SAL Table
enabled
An enabled trigger executes its trigger action if a triggering statement is issued and the trigger restriction (if any) evaluates to TRUE.
disabled
A disabled trigger does not execute its trigger action, even if a triggering statement is issued and the trigger restriction (if any) would evaluate to TRUE.
For enabled triggers, Oracle automatically
Oracle uses the following execution model to maintain the proper firing sequence of multiple triggers and constraint checking:
1. Execute all BEFORE statement triggers that apply to the statement.
2. Loop for each row affected by the SQL statement.
a. Execute all BEFORE row triggers that apply to the
statement.
b. Lock and change row, and perform integrity constraint
checking (The lock is not released until the
transaction is committed.)
c. Execute all AFTER row triggers that apply to the
statement.
3. Complete deferred integrity constraint checking.
4. Execute all AFTER statement triggers that apply to the statement.
The definition of the execution model is recursive. For example, a given SQL statement can cause a BEFORE row trigger to be fired and an integrity constraint to be checked. That BEFORE row trigger, in turn, might perform an update that causes an integrity constraint to be checked and an AFTER statement trigger to be fired. The AFTER statement trigger causes an integrity constraint to be checked. In this case, the execution model executes the steps recursively, as follows:
1. Original SQL statement issued.
2. BEFORE row triggers fired.
3. AFTER statement triggers fired by UPDATE in
BEFORE row trigger.
4. Statements of AFTER statement triggers
executed.
5. Integrity constraint on tables changed by
AFTER statement triggers checked.
6. Statements of BEFORE row triggers executed.
7. Integrity constraint on tables changed by
BEFORE row triggers checked.
8. SQL statement executed.
9. Integrity constraint from SQL statement checked.
For example, in the previously outlined scenario, suppose that Steps 1 through 8 succeed; however, in Step 9 the integrity constraint is violated. As a result of this violation, all changes made by the SQL statement (in Step 8), the fired BEFORE row trigger (in Step 6), and the fired AFTER statement trigger (in Step 4) are rolled back.
Note: Be aware that triggers of different types are fired in a specific order. However, triggers of the same type for the same statement are not guaranteed to fire in any specific order. For example, all BEFORE ROW triggers for a single UPDATE statement may not always fire in the same order. Design your applications not to rely on the firing order of multiple triggers of the same type.
Example
Assume that the SALARY_CHECK trigger (body) includes the following SELECT statement:
SELECT minsal, maxsal INTO minsal, maxsal
FROM salgrade
WHERE job_classification = :new.job_classification;
For this example, assume that transaction T1 includes an update to the MAXSAL column of the SALGRADE table. At this point, the SALARY_CHECK trigger is fired by a statement in transaction T2. The SELECT statement within the fired trigger (originating from T2) does not see the update by the uncommitted transaction T1, and the query in the trigger returns the old MAXSAL value as of the read-consistent point for transaction T2.
Example
Assume the following definition of the TOTAL_SALARY trigger, a trigger to maintain a derived column that stores the total salary of all members in a department:
CREATE TRIGGER total_salary
AFTER DELETE OR INSERT OR UPDATE OF deptno, sal ON emp
FOR EACH ROW BEGIN
/* assume that DEPTNO and SAL are non-null fields */
IF DELETING OR (UPDATING AND :old.deptno != :new.deptno)
THEN UPDATE dept
SET total_sal = total_sal - :old.sal
WHERE deptno = :old.deptno;
END IF;
IF INSERTING OR (UPDATING AND :old.deptno != :new.deptno)
THEN UPDATE dept
SET total_sal = total_sal + :new.sal
WHERE deptno = :new.deptno;
END IF;
IF (UPDATING AND :old.deptno = :new.deptno AND
:old.sal != :new.sal )
THEN UPDATE dept
SET total_sal = total_sal - :old.sal + :new.sal
WHERE deptno = :new.deptno;
END IF;
END;
For this example, suppose that one user's uncommitted transaction includes an update to the TOTAL_SAL column of a row in the DEPT table. At this point, the TOTAL_SALARY trigger is fired by a second user's SQL statement. Because the uncommitted transaction of the first user contains an update to a pertinent value in the TOTAL_SAL column (in other words, a row lock is being held), the updates performed by the TOTAL_SALARY trigger are not executed until the transaction holding the row lock is committed or rolled back. Therefore, the second user waits until the commit or rollback point of the first user's transaction.
![]() ![]() Prev Next |
![]() Copyright © 1996 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |