CREATE TRIGGER print_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON emp
FOR EACH ROW
WHEN (new.empno > 0)
DECLARE
sal_diff number;
BEGIN
sal_diff := new.sal - old.sal;
dbms_output.put('Old salary: ' || :old.sal);
dbms_output.put(' New salary: ' || :new.sal);
dbms_output.put_line(' Difference ' || sal_diff);
END;
/If you enter a SQL statement such as
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
the trigger will fire once for each row that is updated, and it prints the new and old salaries, and the difference.
The CREATE (or CREATE OR REPLACE) statement fails if any errors exist in the PL/SQL block.
The following sections use this example to illustrate the way that parts of a trigger are specified. For more realistic examples of CREATE TRIGGER statements, see "Examples of Trigger Applications" ![[*]](jump.gif)
.
Additional Information: The location of this file is operating system dependent; see your platform-specific Oracle documentation.
Note: AFTER row triggers are slightly more efficient than BEFORE row triggers. With BEFORE row triggers, affected data blocks must be read (logical read, not physical read) once for the trigger and then again for the triggering statement. Alternatively, with AFTER row triggers, the data blocks need only be read once for both the triggering statement and the trigger.
fires after any DELETE, INSERT, or UPDATE on the EMP table. Any of the following statements would trigger the PRINT_SALARY_CHANGES trigger given in the previous example:
DELETE FROM emp; INSERT INTO emp VALUES ( . . . ); INSERT INTO emp SELECT . . . FROM . . . ; UPDATE emp SET . . . ;
The previous example of the PRINT_SALARY_CHANGES trigger might have included a column list in the triggering statement, as in
. . . BEFORE DELETE OR INSERT OR UPDATE OF ename ON emp . . .
For example, you define the following trigger:
CREATE TRIGGER log_salary_increase
AFTER UPDATE ON emp
FOR EACH ROW
WHEN (new.sal > 1000)
BEGIN
INSERT INTO emp_log (emp_id, log_date, new_salary, action)
VALUES (:new.empno, SYSDATE, :new.sal, 'NEW SAL');
END;and then issue the SQL statement
UPDATE emp SET sal = sal + 1000.0
WHERE deptno = 20;If there are five employees in department 20, the trigger will fire five times when this statement is issued, since five rows are affected.
The following trigger fires only once for each UPDATE of the EMP table:
CREATE TRIGGER log_emp_update
AFTER UPDATE ON emp
BEGIN
INSERT INTO emp_log (log_date, action)
VALUES (SYSDATE, 'EMP COMMISSIONS CHANGED');
END;For the order of trigger firing, see the Oracle7 Server Concepts manual.
For example, in the PRINT_SALARY_CHANGES trigger, the trigger body would not be executed if the new value of EMPNO is zero, NULL, or negative. In more realistic examples, you might test if one column value is less than another.
The expression in a WHEN clause of a row trigger can include correlation names, which are explained below. The expression in a WHEN clause must be a SQL expression and cannot include a subquery. You cannot use a PL/SQL expression (including user-defined functions) in the WHEN clause.
The new column values are referenced using the NEW qualifier before the column name, while the old column values are referenced using the OLD qualifier before the column name. For example, if the triggering statement is associated with the EMP table (with the columns SAL, COMM, etc.), you can include statements in the trigger body similar to
IF :new.sal > 10000 . . . IF :new.sal < :old.sal . . .
Old and new values are available in both BEFORE and AFTER row triggers. A NEW column value can be assigned in a BEFORE row trigger, but not in an AFTER row trigger (because the triggering statement takes effect before an AFTER row trigger is fired). If a BEFORE row trigger changes the value of NEW.COLUMN, an AFTER row trigger fired by the same statement sees the change assigned by the BEFORE row trigger.
Correlation names can also be used in the Boolean expression of a WHEN clause. A colon must precede the OLD and NEW qualifiers when they are used in a trigger's body, but a colon is not allowed when using the qualifiers in the WHEN clause or the REFERENCING option.
CREATE TRIGGER PRINT_SALARY_CHANGES BEFORE UPDATE ON new REFERENCING new AS newest FOR EACH ROW BEGIN :newest.field2 := TO_CHAR (:newest.field1); END;
Notice that the NEW qualifier is renamed to NEWEST using the REFERENCING option, and is then used in the trigger body.
INSERT OR UPDATE ON emp
Within the code of the trigger body, you can include the following conditions:
IF INSERTING THEN . . . END IF; IF UPDATING THEN . . . END IF;
The first condition evaluates to TRUE only if the statement that fired the trigger is an INSERT statement; the second condition evaluates to TRUE only if the statement that fired the trigger is an UPDATE statement.
In an UPDATE trigger, a column name can be specified with an UPDATING conditional predicate to determine if the named column is being updated. For example, assume a trigger is defined as
CREATE TRIGGER . . .
. . . UPDATE OF sal, comm ON emp . . .
BEGIN
. . . IF UPDATING ('SAL') THEN . . . END IF;
END;The code in the THEN clause executes only if the triggering UPDATE statement updates the SAL column. The following statement would fire the above trigger and cause the UPDATING (sal) conditional predicate to evaluate to TRUE:
UPDATE emp SET sal = sal + 100;
For more information about error processing in PL/SQL program units, see "Handling Errors" and "Declaring Exceptions and Exception Handling Routines" .
CREATE TRIGGER example
AFTER INSERT ON emp
FOR EACH ROW
BEGIN
INSERT INTO emp@remote -- <- compilation fails here
VALUES ('x'); -- when dblink is inaccessible
EXCEPTION
WHEN OTHERS THEN
INSERT INTO emp_log
VALUES ('x');
END;A trigger is compiled when it is created. Thus, if a remote site is unavailable when the trigger must compile, Oracle cannot validate the statement accessing the remote database, and the compilation fails. The previous example exception statement cannot execute because the trigger does not complete compilation.
Because stored procedures are stored in a compiled form, the work-around for the above example is as follows:
CREATE TRIGGER example
AFTER INSERT ON emp
FOR EACH ROW
BEGIN
insert_row_proc;
END;
CREATE PROCEDURE insert_row_proc
BEGIN
INSERT INTO emp@remote
VALUES ('x');
EXCEPTION
WHEN OTHERS THEN
INSERT INTO emp_log
VALUES ('x');
END;The trigger in this example compiles successfully and calls the stored procedure, which already has a validated statement for accessing the remote database; thus, when the remote INSERT statement fails because the link is down, the exception is caught.
DDL statements are not allowed in the body of a trigger. Also, no transaction control statements are allowed in a trigger. The commands ROLLBACK, COMMIT, and SAVEPOINT cannot be used.
Note: A procedure called by a trigger cannot execute the above transaction control statements because the procedure executes within the context of the trigger body.
Statements inside a trigger can reference remote objects. However, pay special attention when calling remote procedures from within a local trigger; since if a timestamp or signature mismatch is found during execution of the trigger, the remote procedure is not executed and the trigger is invalidated.
When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading. Oracle allows up to 32 triggers to cascade at any one time. However, you can effectively limit the number of trigger cascades using the initialization parameter OPEN_CURSORS, because a cursor must be opened for every execution of a trigger.
Oracle executes all triggers of the same type before executing triggers of a different type. If you have multiple triggers of the same type on a single table, Oracle chooses an arbitrary order to execute these triggers. See the Oracle7 Server Concepts manual for more information on the firing order of triggers.
Each subsequent trigger sees the changes made by the previously fired triggers. Each trigger can see the old and new values. The old values are the original values and the new values are the current values as set by the most recently fired UPDATE or INSERT trigger.
To ensure that multiple triggered actions occur in a specific order, you must consolidate these actions into a single trigger (for example, by having the trigger call a series of procedures).
You cannot open a database that contains multiple triggers of the same type if you are using any version of Oracle before release 7.1, nor can you open such a database if your COMPATIBLE initialization parameter is set to a version earlier than 7.1.0.
Tables are never considered mutating or constraining for statement triggers unless the trigger is fired as the result of a DELETE CASCADE.
For all row triggers, or for statement triggers that were fired as the result of a DELETE CASCADE, there are two important restrictions regarding mutating and constraining tables. These restrictions prevent a trigger from seeing an inconsistent set of data.
Figure 9 - 1. Mutating Tables
Notice that the SQL statement is executed for the first row of the table and then an AFTER ROW trigger is fired. In turn, a statement in the AFTER ROW trigger body attempts to query the original table. However, because the EMP table is mutating, this query is not allowed by Oracle. If attempted, a runtime error occurs, the effects of the trigger body and triggering statement are rolled back, and control is returned to the user or application.
Consider the following trigger:
CREATE OR REPLACE TRIGGER emp_count
AFTER DELETE ON EMP
FOR EACH ROW
DECLARE
n INTEGER;
BEGIN
SELECT COUNT(*) INTO n FROM emp;
DBMS_OUTPUT.PUT_LINE(' There are now ' || n ||
' employees.');
END;If the SQL statement
DELETE FROM emp WHERE empno = 7499;
is issued, the following error is returned:
ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it
Oracle returns this error when the trigger fires since the table is mutating when the first row is deleted. (Only one row is deleted by the statement, since EMPNO is a primary key, but Oracle has no way of knowing that.)
If you delete the line ``FOR EACH ROW'' from the trigger above, the trigger becomes a statement trigger, the table is not mutating when the trigger fires, and the trigger does output the correct data.
If you need to update a mutating or constraining table, you could use a temporary table, a PL/SQL table, or a package variable to bypass these restrictions. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you may be able to use two triggers--an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table.
Declarative integrity constraints are checked at various times with respect to row triggers; see the Oracle7 Server Concepts manual for information about the interaction of triggers and integrity constraints.
Because declarative referential integrity constraints are currently not supported between tables on different nodes of a distributed database, the constraining table restrictions do not apply to triggers that access remote nodes. These restrictions are also not enforced among tables in the same database that are connected by loop-back database links. A loop-back database link makes a local table appear remote by defining a SQL*Net path back to the database that contains the link.
You should not use loop-back database links to circumvent the trigger restrictions. Such applications might behave unpredictably. Trigger restrictions, which were implemented to prevent row triggers from seeing an inconsistent set of data, might be enforced on loop-back database links in future releases of Oracle.
SELECT username FROM USER_USERS
in a trigger, the name of the owner of the trigger is returned, not the name of user who is updating the table.
for details.