Release 8.0
A58227-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8 Concepts Release 8.0 A58227-01 |
|
You may fire when you are ready, Gridley.
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:
Oracle allows you to define procedures that are implicitly executed when an INSERT, UPDATE, or DELETE statement is issued against the associated table. These procedures are called database triggers.
Triggers are similar to stored procedures, discussed in Chapter 17, "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. A procedure is explicitly executed by a user, application, or trigger. Triggers (one or more) 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.
Figure 18-1 shows a database application with some SQL statements that implicitly fire several triggers stored in the database.
Notice that triggers are stored in the database separate 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.
Triggers can supplement the standard capabilities of Oracle to provide a highly customized database management system. For example, a trigger can restrict DML operations against a table to those issued during regular business hours. A trigger could also restrict DML operations to occur only at certain times during weekdays. Other uses for triggers are to
|
Additional Information:
Examples of many of these trigger uses are included in the Oracle8 Application Developer's Guide. |
Triggers are useful for customizing a database. However, you should use triggers only when necessary. The excessive use of triggers can result in complex interdependencies, which may be difficult to maintain in a large application. For example, when a trigger is fired, a SQL statement within its trigger action potentially can fire other triggers, as illustrated in Figure 18-2.
When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading.
You can use both database triggers and integrity constraints to define and enforce any type of integrity rule. However, Oracle Corporation strongly recommends that you use database triggers to constrain data input only in the following situations:
For more information about integrity constraints, see "How Oracle Enforces Data Integrity" on page 24-4.
A trigger has three basic parts:
Figure 18-3 represents each of these parts of a trigger and is not meant to show exact syntax. The sections that follow explain each part of a trigger in greater detail.
A triggering event or statement is the SQL statement that causes a trigger to be fired. A triggering event can be an INSERT, UPDATE, or DELETE statement on a table. For example, in Figure 18-3, the triggering statement is
. . . UPDATE OF parts_on_hand ON inventory . . .
which means: 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. You cannot specify a column list for INSERT and DELETE statements, because they affect entire rows of information.
A triggering event can specify multiple DML statements, as in
. . . INSERT OR UPDATE OR DELETE OF inventory . . .
which means: 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, you can use conditional predicates to detect the type of triggering statement. In this way, you can create a single trigger that executes different code based on the type of statement that fires the trigger.
A trigger restriction specifies a Boolean (logical) expression that must be TRUE for the trigger to fire. The trigger action is not executed if the trigger restriction evaluates to FALSE or UNKNOWN. In the example, the trigger restriction is
new.parts_on_hand < new.reorder_point
A trigger action is the procedure (PL/SQL block) that contains the SQL statements and PL/SQL code to be executed when a triggering statement is issued and the trigger restriction evaluates to TRUE.
Like 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 triggers (described in the next section), 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.
This section describes the different types of triggers:
When you define a trigger, you can specify the number of times the trigger action is to be executed: once for every row affected by the triggering statement (such as might be fired by an UPDATE statement that updates many rows), or once for the triggering statement, no matter how many rows it affects.
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 18-3 illustrates a row trigger that uses the values of each row affected by the triggering statement.
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.
When defining a trigger, you can specify the trigger timing-whether the trigger action is to be executed before or after the triggering statement. BEFORE and AFTER apply to both statement and row triggers. (Another type of trigger is described in "INSTEAD OF Triggers" on page 18-11.)
BEFORE triggers execute the trigger action before the triggering statement is executed. This type of trigger is commonly used in the following situations:
AFTER triggers execute the trigger action after the triggering statement is executed. AFTER triggers are used in the following situations:
Using the options listed above, you can create four types of triggers:
Before executing the triggering statement, the trigger action is executed.
Before modifying each row affected by the triggering statement and before checking appropriate integrity constraints, the trigger action is executed provided that the trigger restriction was not violated.
After executing the triggering statement and applying any deferred integrity constraints, the trigger action is executed.
After modifying each row affected by the triggering statement and possibly applying appropriate integrity constraints, the trigger action is executed for the current row provided the trigger restriction was not violated. Unlike BEFORE row triggers, AFTER row triggers lock rows.
You can have multiple triggers of the same type for the same statement for any given table. For example you may have two BEFORE statement triggers for UPDATE statements on the EMP table. Multiple triggers of the same type permit modular installation of applications that have triggers on the same tables. Also, Oracle snapshot logs use AFTER row triggers, so you can design your own AFTER row trigger in addition to the Oracle-defined AFTER row trigger.
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. The example below 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. 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; /
INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through SQL DML statements (INSERT, UPDATE, and DELETE). These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement. The trigger performs update, insert, or delete operations directly on the underlying tables.
You can write normal INSERT, DELETE, and UPDATE statements against the view and the INSTEAD OF trigger works invisibly in the background to make the right actions take place. By default, INSTEAD OF triggers are activated for each row.
Modifying views has inherent problems of ambiguity.
Object views present additional problems (see Chapter 13, "Object Views"). For example, a key use of object views is to represent master/detail relationships. This inevitably involves joins, but modifying joins is inherently ambiguous.
As a result of these ambiguities, there are many restrictions on which views are modifiable (see the next section). An INSTEAD OF trigger can be used on object views as well as relational views that are not otherwise modifiable.
The mechanism of INSTEAD OF triggers also enables you to modify object view instances on the client-side through OCI. To modify an object materialized by an object view in the client-side object cache and flush it back to the persistent store, you must specify INSTEAD OF triggers, unless the object view is modifiable. If the object is read only, however, it is not necessary to define triggers to pin it.
A view is inherently modifiable if it can be inserted, updated, or deleted without using INSTEAD OF triggers and if it conforms to the restrictions listed below. If the view query contains any of the following constructs, the view is not inherently modifiable and you therefore cannot perform inserts, updates, or deletes on the view:
If a view contains pseudocolumns or expressions, you can only update the view with an UPDATE statement that does not refer to any of the pseudocolumns or expressions.
The following example shows an INSTEAD OF trigger for inserting rows into the MANAGER_INFO view.
CREATE VIEW manager_info AS SELECT e.name, e.empno, d.dept_type, d.deptno, p.level, p.projno FROM emp e, dept d, project p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM emp WHERE emp.empno = :n.empno THEN INSERT INTO emp VALUES(:n.empno, :n.name); ELSE UPDATE emp SET emp.name = :n.name WHERE emp.empno = :n.empno; END IF; IF NOT EXISTS SELECT * FROM dept WHERE dept.deptno = :n.deptno THEN INSERT INTO dept VALUES(:n.deptno, :n.dept_type); ELSE UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno; END IF; IF NOT EXISTS SELECT * FROM project WHERE project.projno = :n.projno THEN INSERT INTO project VALUES(:n.projno, :n.project_level); ELSE UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno; END IF; END;
The actions shown for rows being inserted into the MANAGER_INFO view first test to see if appropriate rows already exist in the base tables from which MANAGER_INFO is derived. The actions then insert new rows or update existing rows, as appropriate. Similar triggers can specify appropriate actions for UPDATE and DELETE.
|
Additional Information:
See the CREATE TRIGGER command in Oracle8 SQL Reference for more information about INSTEAD OF triggers. |
A trigger can be in either of two distinct modes:
For enabled triggers, Oracle automatically
A single SQL statement can potentially fire up to four types of triggers: BEFORE row triggers, BEFORE statement triggers, AFTER row triggers, and AFTER statement triggers. A triggering statement or a statement within a trigger can cause one or more integrity constraints to be checked. Also, triggers can contain statements that cause other triggers to fire (cascading triggers).
Oracle uses the following execution model to maintain the proper firing sequence of multiple triggers and constraint checking:
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 checked on tables changed by AFTER statement triggers.
6. Statements of BEFORE row triggers executed.
7. Integrity constraint checked on tables changed by BEFORE row triggers.
8. SQL statement executed.
9. Integrity constraint from SQL statement checked.
An important property of the execution model is that all actions and checks done as a result of a SQL statement must succeed. If an exception is raised within a trigger, and the exception is not explicitly handled, all actions performed as a result of the original SQL statement, including the actions performed by fired triggers, are rolled back. Thus, integrity constraints cannot be compromised by triggers. The execution model takes into account integrity constraints and disallows triggers that violate declarative integrity constraints.
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.
When a trigger is fired, the tables referenced in the trigger action might be currently undergoing changes by SQL statements in other users' transactions. In all cases, the SQL statements executed within triggers follow the common rules used for standalone SQL statements. In particular, if an uncommitted transaction has modified values that a trigger being fired either needs to read (query) or write (update), the SQL statements in the body of the trigger being fired use the following guidelines:
The following examples illustrate these points.
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.
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.
Oracle stores triggers in their compiled form, just like stored procedures. When a CREATE TRIGGER statement commits, the compiled PL/SQL code, called P code (for pseudocode), is stored in the database and the source code of the trigger is flushed from the shared pool.
For more information about compiling and storing PL/SQL code, see "How Oracle Stores Procedures and Packages" on page 17-15.
Oracle executes a trigger internally using the same steps used for procedure execution. The only subtle difference is that a user has the right to fire a trigger if he or she has the privilege to execute the triggering statement. Other than this, triggers are validated and executed the same way as stored procedures.
For more information, see "How Oracle Executes Procedures and Packages" on page 17-16.
Like procedures, triggers are dependent on referenced objects. Oracle automatically manages the dependencies of a trigger on the schema objects referenced in its trigger action. The dependency issues for triggers are the same as those for stored procedures. Triggers are treated like stored procedures; they are inserted into the data dictionary.
For more information, see Chapter 19, "Oracle Dependency Management".
