Release 2 (8.1.6)
Part Number A76965-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Concepts Release 2 (8.1.6) Part Number A76965-01 |
|
This chapter discusses triggers, which are procedures written in PL/SQL, Java, or C that execute (fire) implicitly whenever a table or view is modified, or when some user actions or database system actions occur. You can write triggers that fire whenever one of the following operations occurs: DML statements on a particular schema object, DDL statements issued within a schema or database, user logon or logoff events, server errors, database startup, or instance shutdown.
This chapter includes:
Oracle allows you to define procedures called triggers that execute implicitly when an INSERT, UPDATE, or DELETE statement is issued against the associated table or, in some cases, against a view, or when database system actions occur. These procedures can be written in PL/SQL or Java and stored in the database, or they can be written as C callouts.
Triggers are similar to stored procedures. A trigger stored in the database can include SQL and PL/SQL or Java 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 are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used.
Figure 19-1 shows a database application with some SQL statements that implicitly fire several triggers stored in the database. Notice that the database stores triggers separately from their associated tables.
A trigger can also call out to a C procedure, which is useful for computationally intensive operations.
The events that fire a trigger include the following:
See Also:
Triggers 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 can also restrict DML operations to occur only at certain times during weekdays. You can also use triggers to:
Although triggers are useful for customizing a database, use them only when necessary. Excessive use of triggers can result in complex interdependencies, which may be difficult to maintain in a large application. For example, when a trigger fires, a SQL statement within its trigger action potentially can fire other triggers, resulting in cascading triggers. This can produce unintended effects. Figure 19-2 illustrates cascading triggers.
You can use both triggers and integrity constraints to define and enforce any type of integrity rule. However, Oracle Corporation strongly recommends that you use triggers to constrain data input only in the following situations:
A trigger has three basic parts:
Figure 19-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, database event, or user event that causes a trigger to fire. A triggering event can be one or more of the following:
For example, in Figure 19-3, the triggering statement is:
. . . UPDATE OF parts_on_hand ON inventory . . .
This statement means that when the PARTS_ON_HAND column of a row in the INVENTORY table is updated, fire the trigger. 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 SQL statements:
. . . INSERT OR UPDATE OR DELETE OF inventory . . .
This part means that when an INSERT, UPDATE, or DELETE statement is issued against the INVENTORY table, fire the trigger. When multiple types of SQL 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 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
Consequently, the trigger does not fire unless the number of available parts is less than a present reorder amount.
A trigger action is the procedure (PL/SQL block, Java program, or C callout) that contains the SQL statements and code to be executed when the following events occur:
Like stored procedures, a trigger action can:
If the triggers are row triggers, the statements in a trigger action have access to column values of the row being processed by the trigger. 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:
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.
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 19-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.
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, use a statement trigger to:
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.
BEFORE and AFTER triggers fired by DML statements can be defined only on tables, not on views. However, triggers on the base tables of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against the view. BEFORE and AFTER triggers fired by DDL statements can be defined only on the database or a schema, not on particular tables.
|
See Also:
|
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.
Using the options listed above, you can create four types of row and statement 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 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.
After executing the triggering statement and applying any deferred integrity constraints, the trigger action is executed.
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. The 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; /
|
Note: INSTEAD OF triggers are available only if you have purchased the Oracle8i Enterprise Edition. They can be used with relational views and object views. See Getting to Know Oracle8i for information about the features available in Oracle8i Enterprise Edition. |
INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through 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.
You can write normal INSERT, UPDATE, and DELETE statements against the view and the INSTEAD OF trigger is fired to update the underlying tables appropriately. INSTEAD OF triggers are activated for each row of the view that gets modified.
Modifying views can have ambiguous results:
Object views present additional problems. For example, a key use of object views is to represent master/detail relationships. This operation 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.
Even if the view is inherently modifiable, you might want to perform validations on the values being inserted, updated or deleted. INSTEAD OF triggers can also be used in this case. Here the trigger code would perform the validation on the rows being modified and if valid, propagate the changes to the underlying tables.
INSTEAD OF triggers also enable 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 inherently modifiable. However, it is not necessary to define these triggers for just pinning and reading the view object in the object cache.
A view is inherently modifiable if data 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 updating rows in the manager_info view, which lists all the departments and their managers.
Let dept be a relational table containing a list of departments,
CREATE TABLE dept ( deptno NUMBER PRIMARY KEY, deptname VARCHAR2(20), manager_num NUMBER );
Let emp be a relational table containing the list of employees and the departments in which they work.
CREATE TABLE emp ( empno NUMBER PRIMARY KEY, empname VARCHAR2(20), deptno NUMBER REFERENCES dept(deptno), startdate DATE ); ALTER TABLE dept ADD (FOREIGN KEY(manager_num) REFERENCES emp(empno));
Create the manager_info view that lists all the managers for each department:
CREATE VIEW manager_info AS SELECT d.deptno, d.deptname, e.empno, e.empname FROM emp e, dept d WHERE e.empno = d.manager_num;
Now, define an INSTEAD OF trigger to handle the inserts on the view. An insert into the manager_info view can be translated into an update to the manager_num column of the dept table.
In the trigger, you can also enforce the constraint that there must be at least one employee working in the department for a person to be a manager of that department.
CREATE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW DECLARE empCount NUMBER; BEGIN /* First check to make sure that the number of employees * in the department is greater than one */ SELECT COUNT(*) INTO empCount FROM emp e WHERE e.deptno = :n.deptno; /* If there are enough employees then make him or her the manager */ IF empCount >= 1 THEN UPDATE dept d SET manager_num = :n.empno WHERE d.deptno = :n.deptno; END IF; END; /
Any inserts to the manager_info view, such as:
INSERT INTO manager_info VALUES (200,'Sports',1002,'Jack');
will fire the manager_info_insert trigger and update the underlying tables. Similar triggers can specify appropriate actions for INSERT and DELETE on the view.
The INSTEAD OF clause to the CREATE TRIGGER statement can only be used for triggers created over views. The BEFORE and AFTER clauses cannot be used for triggers created over views.
The CHECK clause for views is not enforced when inserts or updates to the view are done using INSTEAD OF triggers. The INSTEAD OF trigger body must enforce the check.
|
See Also:
|
You cannot modify the elements of a nested table column in a view directly with the TABLE clause. However, you can do so by defining an INSTEAD OF trigger on the nested table column of the view. The triggers on the nested tables fire if a nested table element is updated, inserted, or deleted and handle the actual modifications to the underlying tables.
Consider the department employee example again. Let the department view contain the list of departments and the set of employees in each department. The following example shows how to modify the elements of the nested table of employee objects in the department view using INSTEAD OF triggers.
/* Create an employee type */ CREATE TYPE emp_t AS OBJECT ( empno NUMBER, empname VARCHAR2(20), days_worked NUMBER ); / /* Create a nested table type of employees */ CREATE TYPE emplist_t AS TABLE OF emp_t; / /* Now, create the department type */ CREATE TYPE dept_t AS OBJECT
( deptno NUMBER, deptname VARCHAR2(20), emplist emplist_t ); / /* The dept_view can now be created based on the dept (department) and emp * (employee) tables. */ CREATE VIEW dept_view OF dept_t WITH OBJECT OID(deptno) AS SELECT d.deptno, d.deptname, -- department number and name CAST (MULTISET ( SELECT e.empno, e.empname, (SYSDATE - e.startdate) FROM emp e WHERE e.deptno = d.deptno) AS emplist_t) -- emplist - nested table of employees FROM dept d;
To be able to insert into the nested table emplist in the view using the TABLE syntax:
INSERT INTO TABLE (SELECT d.emplist FROM dept_view d WHERE d.deptno = 10) VALUES (10,'Harry Mart',334);
You can define an INSTEAD OF trigger on the nested table emplist to handle the insert:
CREATE TRIGGER dept_empinstr INSTEAD OF INSERT ON NESTED TABLE emplist OF dept_view FOR EACH ROW BEGIN INSERT INTO emp VALUES(:NEW.empno, :NEW.empname, :PARENT.deptno, SYSDATE - :NEW.days_worked); END; /
Similarly you can define triggers to handle updates and deletes on the nested table elements.
In regular triggers, the current row's values can be accessed using the NEW and OLD qualifiers. For triggers on nested table columns of views, these qualifiers refer to the attributes of the nested table element being modified. In order to access the values of the parent row containing this nested table column, you can use the PARENT qualifier.
This qualifier can be used only inside these nested table triggers. The parent row's values obtained using this PARENT qualifier cannot be modified (that is, they are read-only).
Consider the dept_empinstr trigger example shown above. The NEW qualifier refers to the row of the nested table being inserted (that is, it contains empno, empname and days_worked) and does not include the department number (deptno) where the employee works. But you need to insert the department number into the employee table inside the trigger. This deptno value can be obtained from the parent row that contains the list of employees, using the PARENT qualifier.
As explained before, if a nested table column in a view has an INSTEAD OF trigger defined over it, then when an element of that nested table is inserted, updated, or deleted, the trigger is fired to do the actual modification.
The view containing the nested table column need not have any INSTEAD OF triggers defined over it for this to work. Any triggers defined on the view will not fire for any modifications to the nested table elements.
Conversely, a statement that modifies a row in the view would only fire the triggers defined on the view and not those on the nested table columns of that view. For instance if the emplist nested table column is updated through the dept_view as in:
UPDATE dept_view SET emplist = emplist_t(emp_t(1001,'John',234));
it will fire the INSTEAD OF update triggers defined over the dept_view, if any, but not the dept_empinstr nested table trigger.
You can use triggers to publish information about database events to subscribers. Applications can subscribe to database events just as they subscribe to messages from other applications. These database events can include:
Triggers on system events can be defined at the database level or schema level. For example, a database shutdown trigger is defined at the database level:
CREATE TRIGGER register_shutdown ON DATABASE SHUTDOWN BEGIN ... DBMS_AQ.ENQUEUE(...); ... END;
Triggers on DDL statements or logon/logoff events can also be defined at the database level or schema level. Triggers on DML statements can be defined on a table or view. A trigger defined at the database level fires for all users, and a trigger defined at the schema or table level fires only when the triggering event involves that schema or table.
Event publication uses the publish-subscribe mechanism of Oracle Advanced Queuing. A queue serves as a message repository for subjects of interest to various subscribers. Triggers use the DBMS_AQ package to enqueue a message when specific system or user events occur.
Each event allows the use of attributes within the trigger text. For example, the database startup and shutdown triggers have attributes for the instance number and the database name, and the logon and logoff triggers have attributes for the username. You can specify a function with the same name as an attribute when you create a trigger if you want to publish that attribute when the event occurs. The attribute's value is then passed to the function or payload when the trigger fires. For triggers on DML statements, the :OLD column values pass the attribute's value to the :NEW column value.
System events that can fire triggers are related to instance startup and shutdown and error messages. Triggers created on startup and shutdown events have to be associated with the database. Triggers created on error events can be associated with the database or with a schema.
User events that can fire triggers are related to user logon and logoff, DDL statements, and DML statements.
LOGON and LOGOFF triggers can be associated with the database or with a schema. Their attributes include the system event and username, and they can specify simple conditions on USERID and USERNAME.
DDL triggers can be associated with the database or with a schema. Their attributes include the system event, the type of schema object, and its name. They can specify simple conditions on the type and name of the schema object, as well as functions like USERID and USERNAME. DDL triggers include the following types of triggers:
DML triggers for event publication are associated with a table. They can be either BEFORE or AFTER triggers that fire for each row on which the specified DML operation occurs. You cannot use INSTEAD OF triggers on views to publish events related to DML statements--instead, you can publish events using BEFORE or AFTER triggers for the DML operations on a view's underlying tables that are caused by INSTEAD OF triggers.
The attributes of DML triggers for event publication include the system event and the columns defined by the user in the SELECT list. They can specify simple conditions on the type and name of the schema object, as well as functions (such as UID, USER, USERENV, and SYSDATE), pseudocolumns, and columns. The columns may be prefixed by :OLD and :NEW for old and new values. Triggers on DML statements include the following triggers:
See Also:
A trigger is in either of two distinct modes:
For enabled triggers, Oracle automatically performs the following actions:
A single SQL statement can potentially fire up to four types of 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.
There are two exceptions to this recursion:
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 that the TOTAL_SALARY trigger maintains 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 PL/SQL triggers in 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.
|
See Also:
"How Oracle Stores Procedures and Packages" for more information about compiling and storing PL/SQL code |
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.
Like procedures, triggers depend 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.
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|