A composite DML trigger can be created on a table or an editable view. It can be triggered at multiple points in time. A 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 section of a compound DML trigger (optional) is used to define variables and subprograms. When the trigger is fired, the declaration section is executed first. The variables and subprograms defined in the declaration section are available during the time period when the trigger statement is in effect.
A compound DML trigger must contain at least one time period, as shown in the following table. Multiple time periods within 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 section. The BEFORE STATEMENT section can be executed before other time periods, so it can be used for initialization. If a compound DML trigger does not have a BEFORE STATEMENT section or an AFTER STATEMENT section, and the trigger statement does not affect any rows, the trigger will never fire.
Limitations of a compound DML trigger
A compound DML trigger has 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 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 using bulk SQL statements and when the trigger statement affects many rows.
In the following example, assume that the statement fires a compound DML trigger with four time periods. The BEFORE EACH ROW and AFTER EACH ROW sections of the trigger are executed for each row in the col1 column of the tbl1 table where col1 is greater than col2. However, the BEFORE STATEMENT section is executed only before the INSERT statement, and the AFTER STATEMENT section is executed 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 accumulating rows, compound DML triggers can send them to other tables for regular bulk data insertion. Using compound DML triggers can also avoid mutating table errors (error code OBE-04091).
Examples
Use a compound trigger to record changes to the employees 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