9 Using Triggers

Do Import and SQL*Loader Fire Triggers?

INSERT triggers fire during SQL*Loader conventional loads. (For direct loads, triggers are disabled before the load.)

The IGNORE parameter of the IMP statement determines whether triggers fire during import operations:

• If IGNORE=N (default) and the table already exists, then import does not change the table and no existing triggers fire.

• If the table does not exist, then import creates and loads it before any triggers are defined, so again no triggers fire.

• If IGNORE=Y, then import loads rows into existing tables. Any existing triggers fire, and indexes are updated to account for the imported data.

How Column Lists Affect UPDATE Triggers

An UPDATE statement might include a list of columns. If a triggering statement includes a column list, the trigger fires only when one of the specified columns is updated. If a triggering statement omits a column list, the trigger fires when any column of the associated table is updated. A column list cannot be specified for INSERT or DELETE triggering statements.

The previous example of the PRINT_SALARY_CHANGES trigger can include a column list in the triggering statement. For example:

... BEFORE DELETE OR INSERT OR UPDATE OF ename ON emp ...

Views that Require INSTEAD OF Triggers

A view cannot be modified by UPDATE, INSERT, or DELETE statements if the view query contains any of the following constructs:

• A set operator

• A DISTINCT operator

• An aggregate or analytic function

• A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause

• A collection expression in a SELECT list

• A subquery in a SELECT list

• A subquery designated WITH READ ONLY

• Joins, with some exceptions, as documented in Oracle Database Administrator's Guide

If a view contains pseudocolumns or expressions, then you can only update the view with an UPDATE statement that does not refer to any of the pseudocolumns or expressions.

INSTEAD OF triggers provide the means to modify object view instances on the client-side through OCI calls.

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, then it is not necessary to define triggers to pin it.

Triggers on Nested Table View Columns

INSTEAD OF triggers can also be created over nested table view columns. These triggers provide a way of updating elements of the nested table. They fire for each nested table element being modified. The row correlation variables inside the trigger correspond to the nested table element. This type of trigger also provides an additional correlation name for accessing the parent row that contains the nested table being modified.

For example, consider a department view that contains a nested table of employees.

CREATE OR REPLACE VIEW Dept_view AS
  SELECT d.Deptno, d.Dept_type, d.Dname,
    CAST (MULTISET ( SELECT e.Empno, e.Empname, e.Salary)
      FROM emp e
        WHERE e.Deptno = d.Deptno) AS Amp_list_ Emplist
      FROM dept d;

The CAST (MULTISET) operator creates a multiset of employees for each department. To modify the emplist column, which is the nested table of employees, define an INSTEAD OF trigger over the column to handle the operation.

The following example shows how an insert trigger might be written:

CREATE OR REPLACE TRIGGER Dept_emplist_tr
  INSTEAD OF INSERT ON NESTED TABLE Emplist OF Dept_view
    REFERENCING NEW AS Employee
      PARENT AS Department
        FOR EACH ROW
BEGIN
  -- Insert on nested table translates to insert on base table:
  INSERT INTO emp VALUES (:Employee.Empno,
    :Employee.Ename,:Employee.Sal, :Department.Deptno);
END;

Any INSERT into the nested table fires the trigger, and the emp table is filled with the correct values. For example:

INSERT INTO TABLE (SELECT d.Emplist FROM Dept_view d WHERE Deptno = 10)
  VALUES (1001, 'John Glenn', 10000);

The :department.deptno correlation variable in this example has the value 10.

Example: INSTEAD OF Trigger

CREATE TABLE Project_tab (
  Prj_level NUMBER,
  Projno    NUMBER,
  Resp_dept NUMBER);
CREATE TABLE emp (
  Empno     NUMBER NOT NULL,
  Ename     VARCHAR2(10),
  Job       VARCHAR2(9),
  Mgr       NUMBER(4),
  Hiredate  DATE,
  Sal       NUMBER(7,2),
  Comm      NUMBER(7,2),
  Deptno    NUMBER(2) NOT NULL);

CREATE TABLE dept (
  Deptno    NUMBER(2) NOT NULL,
  Dname     VARCHAR2(14),
  Loc       VARCHAR2(13),
  Mgr_no    NUMBER,
  Dept_type NUMBER);

The following example shows an INSTEAD OF trigger for inserting rows into the MANAGER_INFO view.

CREATE OR REPLACE VIEW manager_info AS
  SELECT e.ename, e.empno, d.dept_type, d.deptno, p.prj_level,  p.projno
    FROM emp e, dept d, Project_tab p
      WHERE e.empno =  d.mgr_no
        AND d.deptno = p.resp_dept;

CREATE OR REPLACE TRIGGER manager_info_insert
  INSTEAD OF INSERT ON manager_info
    REFERENCING NEW AS n  -- new manager information
      FOR EACH ROW
DECLARE
  rowcnt number;
BEGIN
  SELECT COUNT(*) INTO rowcnt FROM emp WHERE empno = :n.empno;
  IF rowcnt = 0  THEN
    INSERT INTO emp (empno,ename) VALUES (:n.empno, :n.ename);
  ELSE
    UPDATE emp SET emp.ename = :n.ename WHERE emp.empno = :n.empno;
  END IF;
  SELECT COUNT(*) INTO rowcnt FROM dept WHERE deptno = :n.deptno;
  IF rowcnt = 0 THEN
    INSERT INTO dept (deptno, dept_type) 
      VALUES(:n.deptno, :n.dept_type);
  ELSE
    UPDATE dept SET dept.dept_type = :n.dept_type
      WHERE dept.deptno = :n.deptno;
  END IF;
  SELECT COUNT(*) INTO rowcnt FROM Project_tab
    WHERE Project_tab.projno = :n.projno;
  IF rowcnt = 0 THEN
    INSERT INTO Project_tab (projno, prj_level) 
      VALUES(:n.projno, :n.prj_level);
  ELSE
    UPDATE Project_tab SET Project_tab.prj_level = :n.prj_level
      WHERE Project_tab.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.

Why Use Compound Triggers?

The compound trigger makes it easier to program an approach where you want the actions you implement for the various timing points to share common data. To achieve the same effect with simple triggers, you had to model the common state with an ancillary package. This approach was both cumbersome to program and subject to memory leak when the triggering statement caused an error and the after-statement trigger did not fire.

A compound trigger has a declaration section and a section for each of its timing points (see Example 9-2). All of these sections can access a common PL/SQL state. The common state is established when the triggering statement starts and is destroyed when the triggering statement completes, even when the triggering statement causes an error.

CREATE TRIGGER compound_trigger
  FOR UPDATE OF sal ON emp
    COMPOUND TRIGGER

  -- Declaration Section
  -- Variables declared here have firing-statement duration.
  threshold CONSTANT SIMPLE_INTEGER := 200;

  BEFORE STATEMENT IS
  BEGIN
    ...
  END BEFORE STATEMENT;

  BEFORE EACH ROW IS
  BEGIN
    ...
  END BEFORE EACH ROW;

  AFTER EACH ROW IS
  BEGIN
    ...
  END AFTER EACH ROW;
END compound_trigger;
/

Two common reasons to use compound triggers are:

• To accumulate rows destined for a second table so that you can periodically bulk-insert them (as in "Compound Trigger Example")

• To avoid the mutating-table error (ORA-04091) (as in "Using Compound Triggers to Avoid Mutating-Table Error")

Compound Trigger Sections

A compound trigger has a declaration section and at least one timing-point section.

The declaration section (the first section) declares variables and subprograms that timing-point sections can use. When the trigger fires, the declaration section executes before any timing-point sections execute. Variables and subprograms declared in this section have firing-statement duration.

A compound trigger defined on a view has an INSTEAD OF EACH ROW timing-point section, and no other timing-point section.

A compound trigger defined on a table has one or more of the timing-point sections described in Table 9-1. Timing-point sections must appear in the order shown in Table 9-1. If a timing-point section is absent, nothing happens at its timing point.

A timing-point section cannot be enclosed in a PL/SQL block.

Table 9-1 summarizes the timing point sections of a compound trigger that can be defined on a table.

Any section can include the functions Inserting, Updating, Deleting, and Applying.

Triggering Statements of Compound Triggers

The triggering statement of a compound trigger must be a DML statement.

If the triggering statement affects no rows, and the compound trigger has neither a BEFORE STATEMENT section nor an AFTER STATEMENT section, the trigger never fires.

It is when the triggering statement affects many rows that a compound trigger has a performance benefit. This is why it is important to use the BULK COLLECT clause with the FORALL statement. For example, without the BULK COLLECT clause, a FORALL statement that contains an INSERT statement simply performs a single-row insertion operation many times, and you get no benefit from using a compound trigger. For more information about using the BULK COLLECT clause with the FORALL statement, see "Using FORALL and BULK COLLECT Together".

If the triggering statement of a compound trigger is an INSERT statement that includes a subquery, the compound trigger retains some of its performance benefit. Its BEFORE EACH ROW and AFTER EACH ROW sections execute once for each affected row, but its BEFORE STATEMENT and AFTER STATEMENT sections each execute only once (before and after the INSERT statement executes, respectively).

Compound Trigger Restrictions

• The body of a compound trigger must be a compound trigger block.

• A compound trigger must be a DML trigger.

• A compound trigger must be defined on either a table or a view.

• The declaration section cannot include PRAGMA AUTONOMOUS_TRANSACTION.

• A compound trigger body cannot have an initialization block; therefore, it cannot have an exception section.

  This is not a problem, because the BEFORE STATEMENT section always executes exactly once before any other timing-point section executes.

• An exception that occurs in one section must be handled in that section. It cannot transfer control to another section.

• If a section includes a GOTO statement, the target of the GOTO statement must be in the same section.

• :OLD, :NEW, and :PARENT cannot appear in the declaration section, the BEFORE STATEMENT section, or the AFTER STATEMENT section.

• Only the BEFORE EACH ROW section can change the value of :NEW.

• If, after the compound trigger fires, the triggering statement rolls back due to a DML exception:

  • Local variables declared in the compound trigger sections are re-initialized, and any values computed thus far are lost.

  • Side effects from firing the compound trigger are not rolled back.

• The firing order of compound triggers is not guaranteed. Their firing can be interleaved with the firing of simple triggers.

• If compound triggers are ordered using the FOLLOWS option, and if the target of FOLLOWS does not contain the corresponding section as source code, the ordering is ignored.

Example: Modifying LOB Columns with a Trigger

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 ('<h1>HTML Document Fragment</h1><p>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, we couldn't change the new value for a LOB.
-- Now, we 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('<hr><p>Standard footer paragraph.');

  dbms_output.put_line('Final value of CLOB column: '||:NEW.c1);
end;
/ 

set serveroutput on;
update tab1 set c1 = '<h1>Different Document Fragment</h1><p>Different text.';

select * from tab1;

INSTEAD OF Triggers on Nested Table View Columns

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.

Avoiding Trigger Name Conflicts (REFERENCING Option)

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 you have a table named new with columns field1 (number) and field2 (character). 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 TABLE new (
   field1     NUMBER, 
   field2     VARCHAR2(20));

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.

Detecting the DML Operation that Fired a Trigger

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.

Error Conditions and Exceptions in the Trigger Body

If a predefined or user-defined error condition or 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:

• Database startup and shutdown operations do not fail even if the system triggers for these events raise exceptions. Only the trigger action is rolled back. The error is logged in trace files and the alert log.

• If the system trigger is a database logon trigger and the user has ADMINISTER DATABASE TRIGGER privilege, then the user is able to log on successfully even if the trigger raises an exception. For schema logon triggers, if the user logging on is the trigger owner or has ALTER ANY TRIGGER privileges then logon is permitted. Only the trigger action is rolled back and an error is logged in the trace files and alert log.

Maximum Trigger Size

The size of a trigger cannot be more than 32K.

SQL Statements Allowed in Trigger Bodies

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.

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.

Trigger Restrictions on LONG and LONG RAW Datatypes

LONG and LONG RAW datatypes in triggers are subject to the following restrictions:

• A SQL statement within a trigger can insert data into a column of LONG or LONG RAW datatype.

• If data from a LONG or LONG RAW column can be converted to a constrained datatype (such as CHAR and VARCHAR2), then a LONG or LONG RAW column can be referenced in a SQL statement within a trigger. The maximum length for these datatypes is 32000 bytes.

• Variables cannot be declared using the LONG or LONG RAW datatypes.

• :NEW and :PARENT cannot be used with LONG or LONG RAW columns.

Trigger Restrictions on Mutating Tables

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 need to 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.

Restrictions on Mutating Tables Relaxed

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 re-use 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.

System Trigger Restrictions

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. Oracle Database does not fire a trigger that is not committed.

Foreign Function Callouts

All restrictions on foreign function callouts also apply.

Foreign Key Trigger for Child Table

The following trigger guarantees that before an INSERT or UPDATE statement affects a foreign key value, the corresponding value exists in the parent key. The mutating table exception included in the following example allows this trigger to be used with the UPDATE_SET_DEFAULT and UPDATE_CASCADE triggers. This exception can be removed if this trigger is used alone.

CREATE OR REPLACE TRIGGER Emp_dept_check
  BEFORE INSERT OR UPDATE OF Deptno ON emp
    FOR EACH ROW WHEN (new.Deptno IS NOT NULL)

  -- Before row is inserted or DEPTNO is updated in emp table,
  -- fire this trigger to verify that new foreign key value (DEPTNO)
  -- is present in dept table.
DECLARE
  Dummy              INTEGER;  -- Use for cursor fetch
  Invalid_department EXCEPTION;
  Valid_department   EXCEPTION;
  Mutating_table     EXCEPTION;
  PRAGMA EXCEPTION_INIT (Mutating_table, -4091);

  -- Cursor used to verify parent key value exists.
  -- If present, lock parent key's row so it cannot be deleted
  -- by another transaction until this transaction is
  -- committed or rolled back.
  CURSOR Dummy_cursor (Dn NUMBER) IS
    SELECT Deptno FROM dept
      WHERE Deptno = Dn
        FOR UPDATE OF Deptno;
BEGIN
  OPEN Dummy_cursor (:new.Deptno);
  FETCH Dummy_cursor INTO Dummy;

  -- Verify parent key.
  -- If not found, raise user-specified error number & message.
  -- If found, close cursor before allowing triggering statement to complete:
  IF Dummy_cursor%NOTFOUND THEN
    RAISE Invalid_department;
  ELSE
    RAISE valid_department;
  END IF;
  CLOSE Dummy_cursor;
EXCEPTION
  WHEN Invalid_department THEN
    CLOSE Dummy_cursor;
    Raise_application_error(-20000, 'Invalid Department'
      || ' Number' || TO_CHAR(:new.deptno));
  WHEN Valid_department THEN
    CLOSE Dummy_cursor;
  WHEN Mutating_table THEN
    NULL;
END;

UPDATE and DELETE RESTRICT Trigger for Parent Table

The following trigger is defined on the dept table to enforce the UPDATE and DELETE RESTRICT referential action on the primary key of the dept table:

CREATE OR REPLACE TRIGGER Dept_restrict
  BEFORE DELETE OR UPDATE OF Deptno ON dept
    FOR EACH ROW

-- Before row is deleted from dept or primary key (DEPTNO) of dept is updated,
-- check for dependent foreign key values in emp;
-- if any are found, roll back.

DECLARE
  Dummy                 INTEGER;  -- Use for cursor fetch
  Employees_present     EXCEPTION;
  employees_not_present EXCEPTION;

  -- Cursor used to check for dependent foreign key values.
  CURSOR Dummy_cursor (Dn NUMBER) IS
    SELECT Deptno FROM emp WHERE Deptno = Dn;

BEGIN
  OPEN Dummy_cursor (:old.Deptno);
  FETCH Dummy_cursor INTO Dummy;

  -- If dependent foreign key is found, raise user-specified
  -- error number and message. If not found, close cursor
  -- before allowing triggering statement to complete.

  IF Dummy_cursor%FOUND THEN
    RAISE Employees_present;     -- Dependent rows exist
  ELSE
    RAISE Employees_not_present; -- No dependent rows exist
  END IF;
  CLOSE Dummy_cursor;

EXCEPTION
  WHEN Employees_present THEN
    CLOSE Dummy_cursor;
    Raise_application_error(-20001, 'Employees Present in'
      || ' Department ' || TO_CHAR(:old.DEPTNO));
  WHEN Employees_not_present THEN
    CLOSE Dummy_cursor;
END;

UPDATE and DELETE SET NULL Triggers for Parent Table

The following trigger is defined on the dept table to enforce the UPDATE and DELETE SET NULL referential action on the primary key of the dept table:

CREATE OR REPLACE TRIGGER Dept_set_null
  AFTER DELETE OR UPDATE OF Deptno ON dept
    FOR EACH ROW

-- Before row is deleted from dept or primary key (DEPTNO) of dept is updated,
-- set all corresponding dependent foreign key values in emp to NULL:

BEGIN
  IF UPDATING AND :OLD.Deptno != :NEW.Deptno OR DELETING THEN
    UPDATE emp SET emp.Deptno = NULL
      WHERE emp.Deptno = :old.Deptno;
   END IF;
END;

DELETE Cascade Trigger for Parent Table

The following trigger on the dept table enforces the DELETE CASCADE referential action on the primary key of the dept table:

CREATE OR REPLACE TRIGGER Dept_del_cascade
  AFTER DELETE ON dept
    FOR EACH ROW

-- Before row is deleted from dept,
-- delete all rows from emp table whose DEPTNO is same as
-- DEPTNO being deleted from dept table:

BEGIN
  DELETE FROM emp
    WHERE emp.Deptno = :old.Deptno;
END;

UPDATE Cascade Trigger for Parent Table

The following trigger ensures that if a department number is updated in the dept table, then this change is propagated to dependent foreign keys in the emp table:

-- Generate sequence number to be used as flag
-- for determining if update occurred on column:
CREATE SEQUENCE Update_sequence
  INCREMENT BY 1 MAXVALUE 5000 CYCLE;

CREATE OR REPLACE PACKAGE Integritypackage AS
  Updateseq NUMBER;
END Integritypackage;

CREATE OR REPLACE PACKAGE BODY Integritypackage AS
END Integritypackage;

-- Create flag col:
ALTER TABLE emp ADD Update_id NUMBER;

CREATE OR REPLACE TRIGGER Dept_cascade1 BEFORE UPDATE OF Deptno ON dept
DECLARE

-- Before updating dept table (this is a statement trigger),
-- generate new sequence number
-- & assign it to public variable UPDATESEQ of
-- user-defined package named INTEGRITYPACKAGE:
BEGIN
  Integritypackage.Updateseq := Update_sequence.NEXTVAL;

END;

CREATE OR REPLACE TRIGGER Dept_cascade2
  AFTER DELETE OR UPDATE OF Deptno ON dept
    FOR EACH ROW

-- For each department number in dept that is updated,
-- cascade update to dependent foreign keys in emp table.
-- Cascade update only if child row was not already updated by this trigger:
BEGIN
  IF UPDATING THEN
    UPDATE emp
      SET Deptno = :new.Deptno,
        Update_id = Integritypackage.Updateseq   --from 1st
          WHERE emp.Deptno = :old.Deptno
            AND Update_id IS NULL;
            /* Only NULL if not updated by 3rd trigger
            fired by same triggering statement */
   END IF;
   IF DELETING THEN
     -- Before row is deleted from dept,
     -- delete all rows from emp table whose DEPTNO is same as
     -- DEPTNO being deleted from dept table:
     DELETE FROM emp
       WHERE emp.Deptno = :old.Deptno;
   END IF;
END;

CREATE OR REPLACE TRIGGER Dept_cascade3 AFTER UPDATE OF Deptno ON dept
BEGIN UPDATE emp
  SET Update_id = NULL
    WHERE Update_id = Integritypackage.Updateseq;
END;

Trigger for Complex Check Constraints

Triggers can enforce integrity rules other than referential integrity. For example, this trigger performs a complex check before allowing the triggering statement to run.

CREATE OR REPLACE TABLE Salgrade (
  Grade               NUMBER,
  Losal               NUMBER,
  Hisal               NUMBER,
  Job_classification  NUMBER);

CREATE OR REPLACE TRIGGER Salary_check
  BEFORE INSERT OR UPDATE OF Sal, Job ON Emp99
    FOR EACH ROW
DECLARE
  Minsal               NUMBER;
  Maxsal               NUMBER;
  Salary_out_of_range  EXCEPTION;

BEGIN
/* Retrieve minimum & maximum salary for employee's new job classification
  from SALGRADE table into MINSAL and MAXSAL: */

  SELECT Minsal, Maxsal INTO Minsal, Maxsal
    FROM Salgrade
      WHERE Job_classification = :new.Job;

/* If employee's new salary is less than or greater than
   job classification's limits, raise exception.
   Exception message is returned and pending INSERT or UPDATE statement
   that fired the trigger is rolled back:*/

  IF (:new.Sal < Minsal OR :new.Sal > Maxsal) THEN
    RAISE Salary_out_of_range;
  END IF;
EXCEPTION
  WHEN Salary_out_of_range THEN
    Raise_application_error (-20300,
      'Salary '||TO_CHAR(:new.Sal)||' out of range for '
      ||'job classification '||:new.Job
      ||' for employee '||:new.Ename);
  WHEN NO_DATA_FOUND THEN
    Raise_application_error(-20322,
      'Invalid Job Classification '
      ||:new.Job_classification);
END;

Complex Security Authorizations and Triggers

Triggers are commonly used to enforce complex security authorizations for table data. Only use triggers to enforce complex security authorizations that cannot be defined using the database security features provided with Oracle Database. For example, a trigger can prohibit updates to salary data of the emp table during weekends, holidays, and nonworking hours.

When using a trigger to enforce a complex security authorization, it is best to use a BEFORE statement trigger. Using a BEFORE statement trigger has these benefits:

• The security check is done before the triggering statement is allowed to run, so that no wasted work is done by an unauthorized statement.

• The security check is performed only once for the triggering statement, not for each row affected by the triggering statement.

This example shows a trigger used to enforce security.

CREATE TABLE Company_holidays (Day DATE);

CREATE OR REPLACE TRIGGER Emp_permit_changes
  BEFORE INSERT OR DELETE OR UPDATE ON Emp99
DECLARE
  Dummy             INTEGER;
  Not_on_weekends   EXCEPTION;
  Not_on_holidays   EXCEPTION;
  Non_working_hours EXCEPTION;
BEGIN
   /* Check for weekends: */
   IF (TO_CHAR(Sysdate, 'DY') = 'SAT' OR
     TO_CHAR(Sysdate, 'DY') = 'SUN') THEN
       RAISE Not_on_weekends;
   END IF;

   /* Check for company holidays: */
   SELECT COUNT(*) INTO Dummy FROM Company_holidays
     WHERE TRUNC(Day) = TRUNC(Sysdate); -- Discard time parts of dates
   IF dummy > 0 THEN
     RAISE Not_on_holidays;
   END IF;

  /* Check for work hours (8am to 6pm): */
  IF (TO_CHAR(Sysdate, 'HH24') < 8 OR
    TO_CHAR(Sysdate, 'HH24') > 18) THEN
      RAISE Non_working_hours;
  END IF;
EXCEPTION
  WHEN Not_on_weekends THEN
    Raise_application_error(-20324,'Might not change '
      ||'employee table during the weekend');
  WHEN Not_on_holidays THEN
    Raise_application_error(-20325,'Might not change '
      ||'employee table during a holiday');
  WHEN Non_working_hours THEN
    Raise_application_error(-20326,'Might not change '
     ||'emp table during nonworking hours');
END;

Transparent Event Logging and Triggers

Triggers are very useful when you want to transparently perform a related change in the database following certain events.

The REORDER trigger example shows a trigger that reorders parts as necessary when certain conditions are met. (In other words, a triggering statement is entered, and the PARTS_ON_HAND value is less than the REORDER_POINT value.)

Derived Column Values and Triggers

Triggers can derive column values automatically, based upon a value provided by an INSERT or UPDATE statement. This type of trigger is useful to force values in specific columns that depend on the values of other columns in the same row. BEFORE row triggers are necessary to complete this type of operation for the following reasons:

• The dependent values must be derived before the INSERT or UPDATE occurs, so that the triggering statement can use the derived values.

• The trigger must fire for each row affected by the triggering INSERT or UPDATE statement.

The following example illustrates how a trigger can be used to derive new column values for a table whenever a row is inserted or updated.

ALTER TABLE Emp99 ADD(
   Uppername   VARCHAR2(20),
   Soundexname VARCHAR2(20));

CREATE OR REPLACE TRIGGER Derived 
BEFORE INSERT OR UPDATE OF Ename ON Emp99

/* Before updating the ENAME field, derive the values for
   the UPPERNAME and SOUNDEXNAME fields. Restrict users
   from updating these fields directly: */
FOR EACH ROW
BEGIN
   :new.Uppername := UPPER(:new.Ename);
   :new.Soundexname := SOUNDEX(:new.Ename);
END;

Building Complex Updatable Views Using Triggers

Views are an excellent mechanism to provide logical windows over table data. However, when the view query gets complex, the system implicitly cannot translate the DML on the view into those on the underlying tables. INSTEAD OF triggers help solve this problem. These triggers can be defined over views, and they fire instead of the actual DML.

Consider a library system where books are arranged under their respective titles. The library consists of a collection of book type objects. The following example explains the schema.

CREATE OR REPLACE TYPE Book_t AS OBJECT
(
   Booknum   NUMBER,
   Title     VARCHAR2(20),
   Author    VARCHAR2(20),
   Available CHAR(1)
);
CREATE OR REPLACE TYPE Book_list_t AS TABLE OF Book_t;

Assume that the following tables exist in the relational schema:

Table Book_table (Booknum, Section, Title, Author, Available)

Library consists of library_table(section).

You can define a complex view over these tables to create a logical view of the library with sections and a collection of books in each section.

CREATE OR REPLACE VIEW Library_view AS
SELECT i.Section, CAST (MULTISET (
   SELECT b.Booknum, b.Title, b.Author, b.Available
   FROM Book_table b
   WHERE b.Section = i.Section) AS Book_list_t) BOOKLIST
FROM Library_table i;

Make this view updatable by defining an INSTEAD OF trigger over the view.

CREATE OR REPLACE TRIGGER Library_trigger INSTEAD OF INSERT ON Library_view FOR EACH ROW
   Bookvar BOOK_T;
   i       INTEGER;
BEGIN
   INSERT INTO Library_table VALUES (:NEW.Section);
   FOR i IN 1..:NEW.Booklist.COUNT LOOP
      Bookvar := Booklist(i);
      INSERT INTO book_table
         VALUES ( Bookvar.booknum, :NEW.Section, Bookvar.Title, Bookvar.Author, bookvar.Available);
   END LOOP;
END;
/

The library_view is an updatable view, and any INSERTs on the view are handled by the trigger that fires automatically. For example:

INSERT INTO Library_view VALUES ('History', book_list_t(book_t(121330, 'Alexander', 'Mirth', 'Y');

Similarly, you can also define triggers on the nested table booklist to handle modification of the nested table element.

Fine-Grained Access Control Using Triggers

System triggers can be used to set application context. Application context is a relatively new feature that enhances your ability to implement fine-grained access control. Application context is a secure session cache, and it can be used to store session-specific attributes.

In the example that follows, procedure set_ctx sets the application context based on the user profile. The trigger setexpensectx ensures that the context is set for every user.

CONNECT secdemo/password

CREATE OR REPLACE CONTEXT Expenses_reporting USING Secdemo.Exprep_ctx;

REM =================================================================
REM Creation of the package that implements the context:
REM =================================================================

CREATE OR REPLACE PACKAGE Exprep_ctx AS
 PROCEDURE Set_ctx;
END;

SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY Exprep_ctx IS
   PROCEDURE Set_ctx IS
      Empnum   NUMBER;
      Countrec NUMBER;
      Cc       NUMBER;
      Role     VARCHAR2(20);
   BEGIN

      -- SET emp_number:
      SELECT Employee_id INTO Empnum FROM Employee
         WHERE Last_name = SYS_CONTEXT('userenv', 'session_user');

      DBMS_SESSION.SET_CONTEXT('expenses_reporting','emp_number', Empnum);

      -- SET ROLE:
      SELECT COUNT (*) INTO Countrec FROM Cost_center WHERE Manager_id=Empnum;
      IF (countrec > 0) THEN
         DBMS_SESSION.SET_CONTEXT('expenses_reporting','exp_role','MANAGER');
      ELSE
         DBMS_SESSION.SET_CONTEXT('expenses_reporting','exp_role','EMPLOYEE');
      END IF;

      -- SET cc_number:
      SELECT Cost_center_id INTO Cc FROM Employee
         WHERE Last_name = SYS_CONTEXT('userenv','session_user');
      DBMS_SESSION.SET_CONTEXT(expenses_reporting','cc_number',Cc);
   END;
END;

Call syntax:

CREATE OR REPLACE TRIGGER Secdemo.Setexpseetx
AFTER LOGON ON DATABASE
CALL Secdemo.Exprep_etx.Set_otx