9 PL/SQL Triggers

SQL*Loader and Import

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 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 a specified column 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 last_name ON employees ...

Views that Require INSTEAD OF Triggers

A noneditioning view cannot be modified by UPDATE, INSERT, or DELETE statements if the view query contains any of these 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, dept_view, which contains a nested table of employees:

-- Create type of nested table element:
 
CREATE OR REPLACE TYPE nte
AUTHID DEFINER IS
OBJECT (
  emp_id     NUMBER(6),
  lastname   VARCHAR2(25),
  job        VARCHAR2(10),
  sal        NUMBER(8,2)
);
/
 
-- Created type of nested table:
 
CREATE OR REPLACE TYPE emp_list_ IS
  TABLE OF nte;
/
 
-- Create view:

CREATE OR REPLACE VIEW dept_view AS
  SELECT d.department_id, 
         d.department_name,
         CAST (MULTISET (SELECT e.employee_id, e.last_name, e.job_id, e.salary
                         FROM employees e
                         WHERE e.department_id = d.department_id
                        )
                        AS emp_list_
              ) emplist
  FROM departments d;

The CAST (MULTISET) operator creates a multiset of employees for each department. (For information about the CAST function, see Oracle Database SQL Language Reference.)

To modify the emplist column, which is the nested table of employees, define an INSTEAD OF trigger on the column to handle the operation. For example:

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 employees (
    employee_id,
    last_name,
    email,
    hire_date,
    job_id,
    salary,
    department_id
  )
  VALUES (
    :Employee.emp_id,                      -- employee_id
    :Employee.lastname,                    -- last_name
    :Employee.lastname || '@company.com',  -- email
    SYSDATE,                               -- hire_date
    :Employee.job,                         -- job_id
    :Employee.sal,                         -- salary                    
    :Department.department_id              -- department_id
  );
END;
/

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

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE department_id=10;

Result:

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY
----------- ------------------------- ---------- ----------
        200 Whalen                    AD_ASST          4288
 
1 row selected.

Insert a row into the nested table:

INSERT INTO TABLE (
  SELECT d.emplist 
  FROM dept_view d
  WHERE department_id = 10
)
VALUES (1001, 'Glenn', 'AC_MGR', 10000);

Query employees:

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY
----------- ------------------------- ---------- ----------
        200 Whalen                    AD_ASST          4288
       1001 Glenn                     AC_MGR          10000
 
2 rows selected.

INSTEAD OF Trigger Example

DROP TABLE Project_tab;
CREATE TABLE Project_tab (
  Prj_level NUMBER,
  Projno    NUMBER,
  Resp_dept NUMBER);

DROP TABLE emp;
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);

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

Example 9-2 creates a view and an INSTEAD OF trigger for inserting rows into the 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 exist in the base tables from which MANAGER_INFO is derived. The actions then insert or update rows, as appropriate. Similar triggers can specify appropriate actions for UPDATE and DELETE.

Compound Trigger Uses

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 an optional declarative part and a section for each of its timing points (see Example 9-4). 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 OR REPLACE TRIGGER compound_trigger
  FOR UPDATE OF salary ON employees
  COMPOUND TRIGGER
  
  -- Declarative part (optional)
  -- Variables declared here have firing-statement duration.
  threshold CONSTANT SIMPLE_INTEGER := 200;
  
  BEFORE STATEMENT IS
  BEGIN
    NULL;
  END BEFORE STATEMENT;
 
  BEFORE EACH ROW IS
  BEGIN
    NULL;
  END BEFORE EACH ROW;
 
  AFTER EACH ROW IS
  BEGIN
    NULL;
  END AFTER EACH ROW;
 
  AFTER STATEMENT IS
  BEGIN
    NULL;
  END AFTER STATEMENT;
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 an optional declarative part and at least one timing-point section. If a compound trigger has multiple timing-point sections, each section must be for a different timing point.

The optional declarative part declares variables and subprograms that all of the timing-point sections can use. When the trigger fires, the declarative part runs before any timing-point sections run. Variables and subprograms declared in this section have firing-statement duration.

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

A compound trigger defined on a table or editioning view 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 or editioning view.

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. For example, suppose that a compound trigger is triggered by this statement:

INSERT INTO Target
  SELECT c1, c2, c3
  FROM Source
  WHERE Source.c1 > 0

For each row of Source whose column c1 is greater than zero, the BEFORE EACH ROW and AFTER EACH ROW sections of the compound trigger run. However, the BEFORE STATEMENT and AFTER STATEMENT sections each run only once (before and after the INSERT statement runs, 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 declarative part cannot include the AUTONOMOUS_TRANSACTION pragma.

• 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 runs exactly once before any other timing-point section runs.

• 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 declarative part, 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, a DML exception causes the triggering statement to roll back:

  • 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. For information about firing order, see "Order in Which Triggers Fire".

Compound Trigger Example

Scenario: You want to record every change to hr.employees.salary in a new table, employee_salaries. A single UPDATE statement will update many rows of the table hr.employees; therefore, bulk-inserting rows into employee.salaries is more efficient than inserting them individually.

Solution: Define a compound trigger on updates of the table hr.employees, as in Example 9-5. You do not need a BEFORE STATEMENT section to initialize idx or salaries, because they are state variables, which are initialized each time the trigger fires (even when the triggering statement is interrupted and restarted).

CREATE TABLE employee_salaries (
  employee_id NUMBER NOT NULL,
  change_date DATE   NOT NULL,
  salary NUMBER(8,2) NOT NULL,
  CONSTRAINT pk_employee_salaries PRIMARY KEY (employee_id, change_date),
  CONSTRAINT fk_employee_salaries FOREIGN KEY (employee_id)
    REFERENCES employees (employee_id)
      ON DELETE CASCADE)
/
CREATE OR REPLACE TRIGGER maintain_employee_salaries
  FOR UPDATE OF salary ON employees
    COMPOUND TRIGGER

-- Declarative Part:
-- Choose small threshhold value to show how example works:
  threshhold CONSTANT SIMPLE_INTEGER := 7;

  TYPE salaries_t IS TABLE OF employee_salaries%ROWTYPE INDEX BY SIMPLE_INTEGER;
  salaries  salaries_t;
  idx       SIMPLE_INTEGER := 0;

  PROCEDURE flush_array IS
    n CONSTANT SIMPLE_INTEGER := salaries.count();
  BEGIN
    FORALL j IN 1..n
      INSERT INTO employee_salaries VALUES salaries(j);
    salaries.delete();
    idx := 0;
    DBMS_OUTPUT.PUT_LINE('Flushed ' || n || ' rows');
  END flush_array;

  -- AFTER EACH ROW Section:

  AFTER EACH ROW IS
  BEGIN
    idx := idx + 1;
    salaries(idx).employee_id := :NEW.employee_id;
    salaries(idx).change_date := SYSDATE();
    salaries(idx).salary := :NEW.salary;
    IF idx >= threshhold THEN
      flush_array();
    END IF;
  END AFTER EACH ROW;

  -- AFTER STATEMENT Section:

  AFTER STATEMENT IS
  BEGIN
    flush_array();
  END AFTER STATEMENT;
END maintain_employee_salaries;
/
/* Increase salary of every employee in department 50 by 10%: */

UPDATE employees
  SET salary = salary * 1.1
  WHERE department_id = 50
/

/* Wait two seconds: */

BEGIN
  DBMS_LOCK.SLEEP(2);
END;
/

/* Increase salary of every employee in department 50 by 5%: */

UPDATE employees
  SET salary = salary * 1.05
  WHERE department_id = 50
/

Using Compound Triggers to Avoid Mutating-Table Error

You can use compound triggers to avoid the mutating-table error (ORA-04091) described in "Mutating Table Restriction".

Scenario: A business rule states that an employee's salary increase must not exceed 10% of the average salary for the employee's department. This rule must be enforced by a trigger.

Solution: Define a compound trigger on updates of the table hr.employees, as in Example 9-6. The state variables are initialized each time the trigger fires (even when the triggering statement is interrupted and restarted).

CREATE OR REPLACE TRIGGER Check_Employee_Salary_Raise
  FOR UPDATE OF Salary ON Employees
COMPOUND TRIGGER
  Ten_Percent                 CONSTANT NUMBER := 0.1;
  TYPE Salaries_t             IS TABLE OF Employees.Salary%TYPE;
  Avg_Salaries                Salaries_t;
  TYPE Department_IDs_t       IS TABLE OF Employees.Department_ID%TYPE;
  Department_IDs              Department_IDs_t;

  TYPE Department_Salaries_t  IS TABLE OF Employees.Salary%TYPE
                                INDEX BY VARCHAR2(80);
  Department_Avg_Salaries     Department_Salaries_t;

  BEFORE STATEMENT IS
  BEGIN
    SELECT               AVG(e.Salary), NVL(e.Department_ID, -1)
      BULK COLLECT INTO  Avg_Salaries, Department_IDs
      FROM               Employees e
      GROUP BY           e.Department_ID;
    FOR j IN 1..Department_IDs.COUNT() LOOP
      Department_Avg_Salaries(Department_IDs(j)) := Avg_Salaries(j);
    END LOOP;
  END BEFORE STATEMENT;

  AFTER EACH ROW IS
  BEGIN
    IF :NEW.Salary - :Old.Salary >
      Ten_Percent*Department_Avg_Salaries(:NEW.Department_ID)
    THEN
      Raise_Application_Error(-20000, 'Raise too big');
    END IF;
  END AFTER EACH ROW;
END Check_Employee_Salary_Raise;

Example: Trigger That Modifies LOB Columns

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, as in Example 9-9.

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);

  :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.

Trigger Name Conflicts

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.

Example 9-10 creates a table with the same name as a correlation name, new, and then creates a trigger on that table. To avoid conflict between the table name and the correlation name, the trigger references the correlation name as Newest.

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;
/

Conditional Predicates for Detecting the DML Operation that Fired a Trigger

If multiple types of DML statements can fire a trigger, the trigger body can use these conditional predicates to determine which type of statement fired the trigger:

• INSERTING, whose value is TRUE if and only if an INSERT statement fired the trigger.

• UPDATING, whose value is TRUE if and only if an UPDATE statement fired the trigger.

• UPDATING ('column_name'), whose value is TRUE if and only if an UPDATE statement statement that affected the specified column fired the trigger.

• DELETING, whose value is TRUE if and only if a DELETE statement fired the trigger.

In the code of the trigger body, you can use a conditional predicate wherever you can use a BOOLEAN expression. For example:

IF INSERTING OR UPDATING THEN ... ELSE ... END IF;

CASE
    WHEN INSERTING THEN ... ;
    WHEN UPDATING('salary') THEN ... ;
    WHEN UPDATING('department_id') THEN ... ;
    WHEN DELETING THEN ... ;
  END CASE;

Exceptions Raised in the Trigger Body

If a predefined or user-defined error condition (exception) is raised during the execution of a trigger body, then all effects of both the trigger body and 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 these 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 can 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.

Size Restriction

The size of a trigger cannot exceed 32 K.

SQL Statement Restrictions

A trigger cannot contain transaction control statements. Furthermore, a subprogram invoked by a trigger cannot run transaction control statements, because the subprogram runs in the context of the trigger body.

Only a system trigger can contain these SQL statements:

• CREATETABLE

• ALTERTABLE

• DROP TABLE

• ALTER ... COMPILE

A trigger can contain the SELECT statement only in a cursor declaration.

LONG and LONG RAW Data Type Restrictions

LONG and LONG RAW data types in trigger bodies are subject to these restrictions:

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

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

• Variables cannot be declared using the LONG or LONG RAW data types.

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

Mutating Table Restriction

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".)

CREATE OR REPLACE TRIGGER Emp_count
  AFTER DELETE ON employees
  FOR EACH ROW
DECLARE
  n  INTEGER;
BEGIN
  SELECT COUNT(*) INTO n FROM employees;
  DBMS_OUTPUT.PUT_LINE('There are now ' || n || ' employees.');
END;
/
 
DELETE FROM employees WHERE employee_id = 197;

DELETE FROM employees WHERE employee_id = 197
            *
ERROR at line 1:
ORA-04091: table HR.EMPLOYEES is mutating, trigger/function may not see it
ORA-06512: at "HR.EMP_COUNT", line 4
ORA-04088: error during execution of trigger 'HR.EMP_COUNT'

Without FOR EACH ROW, the trigger is not subject to this restriction, as Example 9-15 shows.

CREATE OR REPLACE TRIGGER Emp_count
  AFTER DELETE ON employees
DECLARE
  n  INTEGER;
BEGIN
  SELECT COUNT(*) INTO n FROM employees;
  DBMS_OUTPUT.PUT_LINE('There are now ' || n || ' employees.');
END;
/
 
DELETE FROM employees WHERE employee_id = 197;

There are now 106 employees.
 
1 row deleted.

If you must update a mutating table, you can bypass these restrictions by using either a temporary table or a compound trigger that has a collection in its declarative part. For example, for 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 for 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 "Mutating Table Restriction" 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, if 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.

DROP TABLE p;
CREATE TABLE p (p1 NUMBER CONSTRAINT pk_p_p1 PRIMARY KEY);

DROP TABLE f;
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;
/

Cascades require care for multiple-row updates. For example, given the tables and trigger in Example 9-16: 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 this 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.

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, the following trigger does not fire after it is created:

CREATE OR REPLACE TRIGGER my_trigger
  AFTER CREATE ON DATABASE
BEGIN
  NULL;
END;
/

Foreign Key Trigger for Child Table

The trigger in Example 9-20 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 Example 9-20 allows the trigger emp_dept_check to be used with the UPDATE_SET_DEFAULT and UPDATE_CASCADE triggers. This exception can be removed if the trigger emp_dept_check 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 Triggers for Parent Table

The trigger in Example 9-21 enforces 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 trigger in Example 9-22 enforces 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 trigger in Example 9-23 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 triggers in Example 9-24 ensure 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 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 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;
/

Triggers for Complex Check Constraints

Triggers can enforce integrity rules other than referential integrity. The trigger in Example 9-25 performs a complex check before allowing the triggering statement to run.

CREATE 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 Emp
  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');
END;
/

Triggers for Complex Security Authorizations

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 the 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.

The trigger in Example 9-26 enforces security.

CREATE TABLE Company_holidays (Day DATE);

CREATE OR REPLACE TRIGGER Emp_permit_changes
  BEFORE INSERT OR DELETE OR UPDATE ON Emp
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;
/

Triggers for Transparent Event Logging

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.)

Triggers for Deriving Column Values

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 these 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 trigger in Example 9-27 derives new column values for a table whenever a row is inserted or updated.

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

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

/* 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;
/

Triggers for Building Complex Updatable Views

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 by title. The library consists of a collection of book type objects:

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;
/

The table Book_table is created and populated like this:

DROP TABLE Book_table;
CREATE TABLE Book_table (
  Booknum    NUMBER,
  Section    VARCHAR2(20),
  Title      VARCHAR2(20),
  Author     VARCHAR2(20),
  Available  CHAR(1)
);
 
INSERT INTO Book_table (
  Booknum, Section, Title, Author, Available
) 
VALUES (
  121001, 'Classic', 'Iliad', 'Homer', 'Y'
);
 
INSERT INTO Book_table (
  Booknum, Section, Title, Author, Available
) 
VALUES ( 
  121002, 'Novel', 'Gone with the Wind', 'Mitchell M', 'N'
);
 
SELECT * FROM Book_table;

Result:

BOOKNUM SECTION              TITLE                AUTHOR               A
---------- -------------------- -------------------- -------------------- -
    121001 Classic              Iliad                Homer                Y
    121002 Novel                Gone with the Wind   Mitchell M           N
 
2 rows selected.

The table Library_table is created and populated like this:

DROP TABLE Library_table;
CREATE TABLE Library_table (Section VARCHAR2(20));
 
INSERT INTO Library_table (Section)
VALUES ('Novel');
 
INSERT INTO Library_table (Section)
VALUES ('Classic');
 
SELECT * FROM Library_table;

Result:

SECTION
--------------------
Novel
Classic
 
2 rows selected.

You can define a complex view over the tables Book_table and Library_table 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;

(For information about the CAST function, see Oracle Database SQL Language Reference.)

Make Library_view updatable by defining an INSTEAD OF trigger on it:

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

Insert a new row into Library_view:

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

See the effect on Library_view:

SELECT * FROM Library_view;

Result:

SECTION
--------------------
BOOKLIST(BOOKNUM, TITLE, AUTHOR, AVAILABLE)
--------------------------------------------------------------------
 
Novel
BOOK_LIST_T(BOOK_T(121002, 'Gone with the Wind', 'Mitchell M', 'N'))
 
Classic
BOOK_LIST_T(BOOK_T(121001, 'Iliad', 'Homer', 'Y'))
 
History
BOOK_LIST_T(BOOK_T(121330, 'Alexander', 'Mirth', 'Y'))
 
 
3 rows selected.

See the effect on Book_table:

SELECT * FROM Book_table;

Result:

BOOKNUM SECTION              TITLE                AUTHOR               A
---------- -------------------- -------------------- -------------------- -
    121001 Classic              Iliad                Homer                Y
    121002 Novel                Gone with the Wind   Mitchell M           N
    121330 History              Alexander            Mirth                Y
 
3 rows selected.

See the effect on Library_table:

SELECT * FROM Library_table;

Result:

SECTION
--------------------
Novel
Classic
History
 
3 rows selected.

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

Triggers for Fine-Grained Access Control

You can use LOGON triggers to run the package associated with an application context. An application context captures session-related information about the user who is logging in to the database. From there, your application can control how much access this user has, based on his or her session information.