A composite DML trigger can be created on a table or an editable view. It can be triggered at multiple points in time. The composite trigger is divided into segments based on the time points. 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 provides only MySQL mode.
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 part of a compound DML trigger (optional) is used to define variables and subprograms. When the trigger is fired, the declaration part is executed first. The variables and subprograms defined in the declaration part exist during the time period when the triggering statement is executed.
A compound DML trigger must have 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 cannot be repeated.
| Time Point | Time Period |
|---|---|
| Before the trigger statement is executed | BEFORE STATEMENT |
| After the trigger statement is executed | 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 have an initialization part. However, you can use the BEFORE STATEMENT part to perform initialization operations because it can run before other time periods. If a compound DML trigger has neither a BEFORE STATEMENT part nor an AFTER STATEMENT part and the triggering statement does not affect any rows, the trigger will never be fired.
Limitations of a compound DML trigger
A compound DML trigger has the following limitations:
- The
OLD,NEW, andPARENTkeywords cannot appear in the declaration part,BEFORE STATEMENTpart, orAFTER STATEMENTpart. - Only the
BEFORE EACH ROWpart can modify the values ofNEW. - An exception raised in one time period cannot be handled in another time period.
- If a
GOTOstatement is included in a time period, the target location of theGOTOstatement must be within the same time period.
Advantages of a compound DML trigger
Compound DML triggers offer performance advantages when used with bulk SQL operations and when the triggering statement affects many rows.
In the following example, assume that the statement triggers a compound DML trigger with four time periods. The BEFORE EACH ROW and AFTER EACH ROW parts of the trigger are executed 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 part is executed only before the INSERT statement is executed, and the AFTER STATEMENT part is executed only after the INSERT statement is executed.
INSERT INTO tbl2
SELECT col1
FROM tbl1
WHERE tbl1.col1 > col2;
In addition, you can combine compound DML triggers with bulk insert statements. After accumulating rows, compound DML triggers can send the data to other tables for regular bulk inserts. Using compound DML triggers also helps avoid mutating table errors (error code ORA-04091).
Examples
Use a compound trigger to record the 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 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 >= threshhold 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_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