Create a composite trigger|V4.1.0| docs|Distributed Database

Create a composite trigger

Last Updated：2023-07-28 02:55:42 Updated

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 fire 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

Composite DML triggers are subject to the following limits:

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 time segment cannot handle the exceptions caused in another time segment.
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 fires, 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. Syntax for creating a simple composite trigger on an uneditable view:

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.

Time node	Time segment
Before the trigger statement runs	BEFORE STATEMENT
After the trigger segment runs	AFTER STATEMENT
Before each row affected by the trigger segment	BEFORE EACH ROW
After each row affected by the trigger segment	AFTER EACH ROW

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

Example: Use a composite trigger to record the changes in the child table of a table.

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

  obclient> delimiter /

  obclient> 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('Refresh' || n || 'Row');
  END flush_proc;

  -- AFTER EACH ROW Time segment:

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

  AFTER STATEMENT IS
  BEGIN
    flush_proc();
  END AFTER STATEMENT;
END maintain_emp_salaries;
/
Query OK, 0 rows affected

obclient> SET SERVEROUTPUT ON;
Query OK, 0 rows affected


/* Increase the salary of each employee in department 50 by 8%.*/
obclient> UPDATE employees
       SET salary = salary * 0.8
       WHERE department_id = 50
      /
Query OK, 10 rows affected
Rows matched: 10  Changed: 10  Warnings: 0

Refresh 7 rows
Flushed 7 rows
Refresh 3 rows
Flushed 3 rows


/* Wait for 3 seconds.*/
obclient> BEGIN
      DBMS_LOCK.SLEEP(3);
      END
     /
Query OK, 1 row affected(3.01 sec)


/* Increase the salary of each employee in department 50 by 1.1%.*/
obclient> UPDATE employees
       SET salary = salary * 1.1
       WHERE department_id = 50
     /
Query OK, 10 rows affected
Rows matched: 10  Changed: 10  Warnings: 0

Refresh 7 rows
Flushed 7 rows
Refresh 3 rows
Flushed 3 rows


/* View the changes of the employee table in the emp_salaries table.*/
obclient> SELECT emp_id, count(*) num
       FROM emp_salaries
       GROUP BY emp_id
     /
+--------+------+
| EMP_ID | NUM  |
+--------+------+
|    120 |    1 |
|    121 |    1 |
|    122 |    1 |
|    123 |    1 |
|    124 |    1 |
|    125 |    1 |
|    126 |    1 |
|    127 |    1 |
|    128 |    1 |
|    129 |    1 |
+--------+------+
10 rows in set