Composite DML triggers can be created on tables or editable views and can be triggered at multiple points in time. Composite triggers are divided into segments based on each trigger point, and each segment contains an independent executable part and an optional exception handling part.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
Syntax of a compound DML trigger
The syntax of a compound DML trigger is as follows:
CREATE trigger_name FOR dml_event_clause ON view_name
COMPOUND TRIGGER
INSTEAD OF EACH ROW IS BEGIN
sql_statement;
END INSTEAD OF EACH ROW;
The declaration section of a compound DML trigger (optional) defines variables and subprograms. When a compound DML trigger is triggered, the declaration section runs first. The variables and subprograms defined in the declaration section exist during the time period specified by the trigger statement.
A compound DML trigger must contain at least one time period, as shown in the following table. Multiple time periods in a compound DML trigger can be arranged in any order, but the time points must be unique.
Time Point |
Time Period |
|---|---|
| Before the trigger statement runs | BEFORE STATEMENT |
| After the trigger statement runs | AFTER STATEMENT |
| Before each row affected by the trigger statement | BEFORE EACH ROW |
| After each row affected by the trigger statement | AFTER EACH ROW |
A compound DML trigger does not contain an initialization section. Because the BEFORE STATEMENT section can run before other time periods, it can be used for initialization. If a compound DML trigger does not contain a BEFORE STATEMENT section or an AFTER STATEMENT section and the trigger statement does not affect any rows, the trigger will never be triggered.
Limitations of compound DML triggers
Compound DML triggers have the following limitations:
- The
OLD,NEW, andPARENTkeywords cannot appear in the declaration section, theBEFORE STATEMENTsection, or theAFTER STATEMENTsection. - Only the
BEFORE EACH ROWsection can modify the value ofNEW. - An exception raised in one time period cannot be handled in another time period.
- If a
GOTOstatement appears in a time period, the target location of theGOTOstatement must be in the same time period.
Advantages of compound DML triggers
Compound DML triggers offer performance advantages for bulk SQL operations and scenarios where the trigger statement affects many rows.
The following example assumes that the statement triggers a compound DML trigger with four time periods. The BEFORE EACH ROW and AFTER EACH ROW sections of the trigger run for each row in the tbl1 table where the value of the col1 column is greater than the value of the col2 column. However, the BEFORE STATEMENT section runs only before the INSERT statement, and the AFTER STATEMENT section runs only after the INSERT statement.
INSERT INTO tbl2
SELECT col1
FROM tbl1
WHERE tbl1.col1 > col2;
In addition, compound DML triggers can be used with bulk insert statements. After a compound DML trigger accumulates rows, it can send them to other tables for regular bulk data insertion. Using compound DML triggers also helps avoid mutating table errors (error code OBE-04091).
Examples
Use a compound trigger to record changes to a table in a subtable.
obclient> CREATE TABLE emp_sal (
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);
Query OK, 0 rows affected
obclient> delimiter /
obclient> CREATE OR REPLACE TRIGGER maintain_emp_sal
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER
threshhold CONSTANT SIMPLE_INTEGER := 3;
TYPE sal_typ IS TABLE OF emp_sal%ROWTYPE INDEX BY SIMPLE_INTEGER;
sal sal_typ;
idx SIMPLE_INTEGER := 0;
PROCEDURE flush_proc IS
n CONSTANT SIMPLE_INTEGER := sal.count();
BEGIN
FORALL j IN 1..n
INSERT INTO emp_sal VALUES sal(j);
sal.delete();
idx := 0;
DBMS_OUTPUT.PUT_LINE('Flushed ' || n || ' rows');
END flush_proc;
-- AFTER EACH ROW timing:
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 >= threshhold THEN
flush_proc();
END IF;
END AFTER EACH ROW;
-- AFTER STATEMENT timing:
AFTER STATEMENT IS
BEGIN
flush_proc();
END AFTER STATEMENT;
END maintain_emp_sal;
/
Query OK, 0 rows affected
obclient> delimiter ;
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
Flushed 3 rows
Flushed 3 rows
Flushed 7 rows
Flushed 7 rows
Flushed 3 rows
Flushed 1 row
Flushed 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
Flushed 3 rows
Flushed 3 rows
Flushed 7 rows
Flushed 7 rows
Flushed 3 rows
Flushed 1 row
Flushed 3 rows
Flushed 3 rows
/* View the changes to the employees table in the emp_sal table */
obclient> SELECT emp_id, count(*) num
FROM emp_sal
GROUP BY emp_id;
+--------+------+
| EMP_ID | NUM |
+--------+------+
| 120 | 2 |
| 121 | 2 |
| 122 | 2 |
| 123 | 2 |
| 124 | 2 |
| 125 | 2 |
| 126 | 2 |
| 127 | 2 |
| 128 | 2 |
| 129 | 2 |
+--------+------+
10 rows in set