Create a composite trigger

A composite trigger can contain one to four types of the before-row, after-row, before-statement, and after-statement triggering events, or contain only one INSTEAD OF triggering event.

Overview

A composite trigger created on a table or editable view can be activated at multiple points in time. A composite trigger is segmented based on each point in time. Each time segment has its own executable part and exception-handling part (optional). All the parts can access a universal procedural language (PL) state. The universal state is created when the trigger statement starts being executed and destroyed after the trigger statement is executed. It is not affected even if the trigger statement causes errors.

Limitations on composite triggers

Compound DML triggers are subject to the following limitations:

• OLD, NEW, and PARENT cannot appear in the declarative part, BEFORE STATEMENT part, or AFTER STATEMENT part.

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

• One timing point section cannot handle the exceptions raised in another timing point section.

• If a time segment contains the GOTO statement, the destination of the GOTO statement must be in the same time segment.

Create a trigger

In the declarative part (optional) of a composite trigger, you can declare the variables and subprograms that can be used by all time segments of the composite trigger. When the composite trigger is activated, the declarative part runs before any time segment. The variables and subprograms exist in the time segments to which the trigger statement applies.

A composite trigger created on an uneditable view is not a real composite trigger because it has only one time segment. The syntax for creating a simple composite trigger on an uneditable view is as follows:

CREATE TRIGGER FOR dml_event_clause ON view_name
[{ FOLLOWS | PRECEDES } other_trigger_name]
COMPOUND TRIGGER
INSTEAD OF EACH ROW IS BEGIN
sql_statement;
END INSTEAD OF EACH ROW;

A composite trigger created on a table or editable view contains at least one time segment. If a trigger contains multiple time segments, they can be sorted in any order but the points in time cannot be duplicated. If a time segment does not exist, no operation will be performed at this point in time.

The following table describes the time segments contained in a composite trigger.

A composite trigger has no initialization part. However, BEFORE STATEMENT can perform any initialization as needed because it runs before other time segments.

If a composite trigger has neither the BEFORE STATEMENT part nor the AFTER STATEMENT part and its trigger statement does not affect any rows, this composite trigger will never activate.

For example, use a composite trigger to record the changes in the child table of a table.

1. Assume there are two tables, employees and emp_salaries.

   obclient [SYS]> CREATE TABLE employees ( 
       employee_id NUMBER PRIMARY KEY, 
       name VARCHAR2(50), 
       salary NUMBER(8,2),
       department_id NUMBER);
   

   obclient [SYS]> CREATE TABLE emp_salaries (
      emp_id NUMBER NOT NULL,
      change_date DATE   NOT NULL,
      salary NUMBER(8,2) NOT NULL,
      FOREIGN KEY (emp_id)
      REFERENCES employees (employee_id) ON DELETE CASCADE);
   

   Data in the employees table is as follows:

   obclient [SYS]> SELECT * FROM employees;
   

   +-------------+------+--------+---------------+
   | EMPLOYEE_ID | NAME | SALARY | DEPARTMENT_ID |
   +-------------+------+--------+---------------+
   |        1001 | zs   |   4400 |            50 |
   |        1002 | li   |   4840 |            50 |
   |        1003 | wong |   5104 |            50 |
   |        1004 | xin  |   5984 |            50 |
   |        1005 | mai  |   5984 |            50 |
   |        1006 | cai  |   5280 |            50 |
   |        1007 | cai  |   7040 |            50 |
   |        1008 | cai  |   7084 |            50 |
   |        1009 | deng |   6644 |            50 |
   |        1010 | yang |   6160 |            50 |
   |        1011 | qiu  |   7000 |            51 |
   +-------------+------+--------+---------------+
   11 rows in set
   

2. Create the trigger maintain_emp_salaries.

    obclient [SYS]> delimiter /
   

   obclient [SYS]> CREATE OR REPLACE TRIGGER maintain_emp_salaries
   FOR UPDATE OF salary ON employees
     COMPOUND TRIGGER
   
   threshold CONSTANT SIMPLE_INTEGER := 7;
   
   TYPE salaries_t IS TABLE OF emp_salaries%ROWTYPE INDEX BY SIMPLE_INTEGER;
   sal  salaries_t;
   idx  SIMPLE_INTEGER := 0;
   
   PROCEDURE flush_proc IS
     n CONSTANT SIMPLE_INTEGER := sal.count();
   BEGIN
     FORALL j IN 1..n
       INSERT INTO emp_salaries VALUES sal(j);
     sal.delete();
     idx := 0;
     DBMS_OUTPUT.PUT_LINE('flush' || n || 'row');
   END flush_proc;
   
   -- AFTER EACH ROW time period:
   
   AFTER EACH ROW IS
   BEGIN
     idx := idx + 1;
     sal(idx).emp_id := :NEW.employee_id;
     sal(idx).change_date := SYSTIMESTAMP;
     sal(idx).salary := :NEW.salary;
     IF idx >= threshold THEN
       flush_proc();
     END IF;
   END AFTER EACH ROW;
   
   -- AFTER STATEMENT time period:
   
   AFTER STATEMENT IS
   BEGIN
     flush_proc();
   END AFTER STATEMENT;
   END maintain_emp_salaries;
   /
   

   obclient [SYS]> SET SERVEROUTPUT ON;
   

3. Increase the salary of each employee in department 50 by 8%.

   obclient [SYS]> UPDATE employees
           SET salary = salary * 1.08
           WHERE department_id = 50
           /
   Query OK, 10 rows affected 
   Rows matched: 10  Changed: 10  Warnings: 0
   
   Flushed 7 rows
   Flushed 3 rows
   

   Wait for 3 seconds and then execute the following command:

   obclient [SYS]> BEGIN
     DBMS_LOCK.SLEEP(3);
     END
    /
   

4. Increase the salary of each employee in department 50 by 10%.

   obclient [SYS]> UPDATE employees
         SET salary = salary * 1.1
         WHERE department_id = 50
       /
   Query OK, 10 rows affected 
   Rows matched: 10  Changed: 10  Warnings: 0
   
   Flushed 7 rows
   Flushed 3 rows
   

5. View the changes in the employee table in the emp_salaries table.

   obclient [SYS]> SELECT emp_id, count(*) num
         FROM emp_salaries
         GROUP BY emp_id
       /
   

   The query result is as follows:

   +--------+------+
   | EMP_ID | NUM  |
   +--------+------+
   |   1001 |    2 |
   |   1002 |    2 |
   |   1003 |    2 |
   |   1004 |    2 |
   |   1005 |    2 |
   |   1006 |    2 |
   |   1007 |    2 |
   |   1008 |    2 |
   |   1009 |    2 |
   |   1010 |    2 |
   +--------+------+
   10 rows in set