Note:
This topic applies primarily to simple triggers. The body of a compound trigger has a different format (see Compound Triggers).
The trigger body is either a CALL subprogram (a PL/SQL subprogram, or a Java subprogram encapsulated in a PL/SQL wrapper) or a PL/SQL block, and as such, it can include SQL and PL/SQL statements. These statements are executed if the triggering statement is entered and if the trigger restriction (if any) evaluates to TRUE.
If the trigger body for a row trigger is a PL/SQL block (not a CALL subprogram), it can include the following constructs:
The LOGON trigger in Example 9-5 executes the procedure sec_mgr.check_user after a user logs onto the database. The body of the trigger includes an exception-handling part, which includes a WHEN OTHERS exception that invokes RAISE_APPLICATION_ERROR.
CREATE OR REPLACE TRIGGER check_user
AFTER LOGON ON DATABASE
BEGIN
sec_mgr.check_user;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR
(-20000, 'Unexpected error: '|| DBMS_Utility.Format_Error_Stack);
END;
/
Although triggers are declared using PL/SQL, they can call subprograms in other languages. The trigger in Example 9-6 invokes a Java subprogram.
CREATE OR REPLACE PROCEDURE Before_delete (Id IN NUMBER, Ename VARCHAR2) IS language Java name 'thjvTriggers.beforeDelete (oracle.sql.NUMBER, oracle.sql.CHAR)'; CREATE OR REPLACE TRIGGER Pre_del_trigger BEFORE DELETE ON Tab FOR EACH ROW CALL Before_delete (:OLD.Id, :OLD.Ename) /
The corresponding Java file is thjvTriggers.java:
import java.sql.*
import java.io.*
import oracle.sql.*
import oracle.oracore.*
public class thjvTriggers
{
public state void
beforeDelete (NUMBER old_id, CHAR old_name)
Throws SQLException, CoreException
{
Connection conn = JDBCConnection.defaultConnection();
Statement stmt = conn.CreateStatement();
String sql = "insert into logtab values
("+ old_id.intValue() +", '"+ old_ename.toString() + ", BEFORE DELETE');
stmt.executeUpdate (sql);
stmt.close();
return;
}
}
Within a trigger body of a row trigger, the PL/SQL code and SQL statements have access to the old and new column values of the current row affected by the triggering statement. Two correlation names exist for every column of the table being modified: one for the old column value, and one for the new column value. Depending on the type of triggering statement, certain correlation names might not have any meaning.
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, and so on), then you can include statements in the trigger body. For example:
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 fires). If a BEFORE row trigger changes the value of NEW.column, then 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 body, but a colon is not allowed when using the qualifiers in the WHEN clause or the REFERENCING option.
You can treat LOB columns the same as other columns, using regular SQL and PL/SQL functions with CLOB columns, and calls to the DBMS_LOB package with BLOB columns:
drop table tab1;
create table tab1 (c1 clob);
insert into tab1 values ('HTML Document Fragment Some text.');
create or replace trigger trg1
before update on tab1
for each row
begin
dbms_output.put_line('Old value of CLOB column: '||:OLD.c1);
dbms_output.put_line('Proposed new value of CLOB column: '||:NEW.c1);
-- Previously, you couldn't change the new value for a LOB.
-- Now, you can replace it, or construct a new value using SUBSTR, INSTR...
-- operations for a CLOB, or DBMS_LOB calls for a BLOB.
:NEW.c1 := :NEW.c1 || to_clob('Standard footer paragraph.');
dbms_output.put_line('Final value of CLOB column: '||:NEW.c1);
end;
/
set serveroutput on;
update tab1 set c1 = 'Different Document Fragment Different text.';
select * from tab1;
In the case of INSTEAD OF triggers on nested table view columns, the NEW and OLD qualifiers correspond to the new and old nested table elements. The parent row corresponding to this nested table element can be accessed using the parent qualifier. The parent correlation name is meaningful and valid only inside a nested table trigger.
The REFERENCING option can be specified in a trigger body of a row trigger to avoid name conflicts among the correlation names and tables that might be named OLD or NEW. Because this is rare, this option is infrequently used.
For example, assume that the table new was created as follows:
CREATE TABLE new ( field1 NUMBER, field2 VARCHAR2(20));
The following CREATE TRIGGER example shows a trigger defined on the new table that can use correlation names and avoid naming conflicts between the correlation names and the table name:
CREATE OR REPLACE 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 it is then used in the trigger body.
If more than one type of DML operation can fire a trigger (for example, ON INSERT OR DELETE OR UPDATE OF emp), the trigger body can use the conditional predicates INSERTING, DELETING, and UPDATING to check which type of statement fire the trigger.
Within the code of the trigger body, you can execute blocks of code depending on the kind of DML operation that fired the trigger:
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 the following:
CREATE OR REPLACE TRIGGER ...
... UPDATE OF Sal, Comm ON emp ...
BEGIN
... IF UPDATING ('SAL') THEN ... END IF;
END;
The code in the THEN clause runs only if the triggering UPDATE statement updates the SAL column. This way, the trigger can minimize its overhead when the column of interest is not being changed.
If a predefined or user-defined error condition (exception) is raised during the execution of a trigger body, then all effects of the trigger body, as well as the triggering statement, are rolled back (unless the error is trapped by an exception handler). Therefore, a trigger body can prevent the execution of the triggering statement by raising an exception. User-defined exceptions are commonly used in triggers that enforce complex security authorizations or constraints.
If the LOGON trigger raises an exception, logon fails except in the following cases:
You can use the OBJECT_VALUE pseudocolumn in a trigger on an object table because, as of 10g Release 1 (10.1), OBJECT_VALUE means the object as a whole. This is one example of its use. You can also invoke a PL/SQL function with OBJECT_VALUE as the data type of an IN formal parameter.
Here is an example of the use of OBJECT_VALUE in a trigger. To keep track of updates to values in an object table tbl, a history table, tbl_history, is also created in the following example. For tbl, the values 1 through 5 are inserted into n, while m is kept at 0. The trigger is a row-level trigger that executes once for each row affected by a DML statement. The trigger causes the old and new values of the object t in tbl to be written in tbl_history when tbl is updated. These old and new values are :OLD.OBJECT_VALUE and :NEW.OBJECT_VALUE. An update of the table tbl is done (each value of n is increased by 1). A select from the history table to check that the trigger works is then shown at the end of the example:
CREATE OR REPLACE TYPE t AS OBJECT (n NUMBER, m NUMBER)
/
CREATE TABLE tbl OF t
/
BEGIN
FOR j IN 1..5 LOOP
INSERT INTO tbl VALUES (t(j, 0));
END LOOP;
END;
/
CREATE TABLE tbl_history ( d DATE, old_obj t, new_obj t)
/
CREATE OR REPLACE TRIGGER Tbl_Trg
AFTER UPDATE ON tbl
FOR EACH ROW
BEGIN
INSERT INTO tbl_history (d, old_obj, new_obj)
VALUES (SYSDATE, :OLD.OBJECT_VALUE, :NEW.OBJECT_VALUE);
END Tbl_Trg;
/
--------------------------------------------------------------------------------
UPDATE tbl SET tbl.n = tbl.n+1
/
BEGIN
FOR j IN (SELECT d, old_obj, new_obj FROM tbl_history) LOOP
Dbms_Output.Put_Line (
j.d||
' -- old: '||j.old_obj.n||' '||j.old_obj.m||
' -- new: '||j.new_obj.n||' '||j.new_obj.m);
END LOOP;
END;
/
The result of the select shows that all values of column n were increased by 1. The value of m remains 0. The output of the select is:
23-MAY-05 -- old: 1 0 -- new: 2 0 23-MAY-05 -- old: 2 0 -- new: 3 0 23-MAY-05 -- old: 3 0 -- new: 4 0 23-MAY-05 -- old: 4 0 -- new: 5 0 23-MAY-05 -- old: 5 0 -- new: 6 0
A trigger that accesses a remote site cannot do remote exception handling if the network link is unavailable. For example:
CREATE OR REPLACE TRIGGER Example
AFTER INSERT ON emp
FOR EACH ROW
BEGIN
When dblink is inaccessible, compilation fails here:
INSERT INTO emp@Remote VALUES ('x');
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, then the database cannot validate the statement accessing the remote database, and the compilation fails. The previous example exception statement cannot run, because the trigger does not complete compilation.
Because stored subprograms are stored in a compiled form, the work-around for the previous example is as follows:
CREATE OR REPLACE TRIGGER Example
AFTER INSERT ON emp
FOR EACH ROW
BEGIN
Insert_row_proc;
END;
CREATE OR REPLACE PROCEDURE Insert_row_proc AS
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 invokes the stored subprogram, 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.
Coding triggers requires some restrictions that are not required for standard PL/SQL blocks.
The size of a trigger cannot be more than 32K.
A trigger body can contain SELECT INTO statements, SELECT statements in cursor definitions, and all other DML statements.
A system trigger body can contain the DDL statements CREATETABLE, ALTERTABLE, DROP TABLE and ALTER COMPILE. A nonsystem trigger body cannot contain DDL or transaction control statements.
Note:
A subprogram invoked by a trigger cannot run the previous transaction control statements, because the subprogram runs within the context of the trigger body.
Statements inside a trigger can reference remote schema objects. However, pay special attention when invoking remote subprograms from within a local trigger. If a timestamp or signature mismatch is found during execution of the trigger, then the remote subprogram is not run, and the trigger is invalidated.
LONG and LONG RAW data types in triggers are subject to the following restrictions:
A mutating table is a table that is being modified by an UPDATE, DELETE, or INSERT statement, or a table that might be updated by the effects of a DELETE CASCADE constraint.
The session that issued the triggering statement cannot query or modify a mutating table. This restriction prevents a trigger from seeing an inconsistent set of data.
This restriction applies to all triggers that use the FOR EACH ROW clause. Views being modified in INSTEAD OF triggers are not considered mutating.
When a trigger encounters a mutating table, a run-time error occurs, the effects of the trigger body and triggering statement are rolled back, and control is returned to the user or application. (You can use compound triggers to avoid the mutating-table error. For more information, see Using Compound Triggers to Avoid Mutating-Table Error.)
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 following SQL statement is entered:
DELETE FROM emp WHERE empno = 7499;
An error is returned because the table is mutating when the row is deleted:
ORA-04091: table HR.emp is mutating, trigger/function might not see it
If you delete the line "FOR EACH ROW" from the trigger, it becomes a statement trigger that is not subject to this restriction, and the trigger.
If you must update a mutating table, you can bypass these restrictions by using a temporary table, a PL/SQL table, or a package variable. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you might 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 constraints are checked at various times with respect to row triggers.
Because declarative referential constraints are not supported between tables on different nodes of a distributed database, the mutating 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 an Oracle Net path back to the database that contains the link.
The mutating error described in Trigger Restrictions on Mutating Tables prevents the trigger from reading or modifying the table that the parent statement is modifying. However, as of Oracle Database Release 8.1, a deletion from the parent table causes BEFORE and AFTER triggers to fire once. Therefore, you can create triggers (just not row triggers) to read and modify the parent and child tables.
This allows most foreign key constraint actions to be implemented through their obvious after-row trigger, providing the constraint is not self-referential. Update cascade, update set null, update set default, delete set default, inserting a missing parent, and maintaining a count of children can all be implemented easily. For example, this is an implementation of update cascade:
CREATE TABLE p (p1 NUMBER CONSTRAINT pk_p_p1 PRIMARY KEY); CREATE TABLE f (f1 NUMBER CONSTRAINT fk_f_f1 REFERENCES p); CREATE TRIGGER pt AFTER UPDATE ON p FOR EACH ROW BEGIN UPDATE f SET f1 = :NEW.p1 WHERE f1 = :OLD.p1; END; /
This implementation requires care for multiple-row updates. For example, if table p has three rows with the values (1), (2), (3), and table f also has three rows with the values (1), (2), (3), then the following statement updates p correctly but causes problems when the trigger updates f:
UPDATE p SET p1 = p1+1;
The statement first updates (1) to (2) in p, and the trigger updates (1) to (2) in f, leaving two rows of value (2) in f. Then the statement updates (2) to (3) in p, and the trigger updates both rows of value (2) to (3) in f. Finally, the statement updates (3) to (4) in p, and the trigger updates all three rows in f from (3) to (4). The relationship of the data in p and f is lost.
To avoid this problem, either forbid multiple-row updates to p that change the primary key and reuse existing primary key values, or track updates to foreign key values and modify the trigger to ensure that no row is updated twice.
That is the only problem with this technique for foreign key updates. The trigger cannot miss rows that were changed but not committed by another transaction, because the foreign key constraint guarantees that no matching foreign key rows are locked before the after-row trigger is invoked.
Depending on the event, different event attribute functions are available. For example, certain DDL operations might not be allowed on DDL events. Check Event Attribute Functions before using an event attribute function, because its effects might be undefined rather than producing an error condition.
Only committed triggers fire. For example, if you create a trigger that fires after all CREATE events, then the trigger itself does not fire after the creation, because the correct information about this trigger was not committed at the time when the trigger on CREATE events fired.
For example, if you execute the following SQL statement:
CREATE OR REPLACE TRIGGER my_trigger AFTER CREATE ON DATABASE BEGIN null; END;
Then, trigger my_trigger does not fire after the creation of my_trigger. The database does not fire a trigger that is not committed.
All restrictions on foreign function callouts also apply.
The following statement, inside a trigger, returns the owner of the trigger, not the name of user who is updating the table:
SELECT Username FROM USER_USERS;
