You can create a compound DML trigger on a table or an editable view. The trigger can fire at multiple timing points. A compound trigger is sectioned based on each timing point. Each timing point section has its own executable part and exception-handling part (optional).
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Syntax
The syntax of the simplest 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 declarative part (optional) of a compound DML trigger defines variables and subprograms. When the trigger fires, the declarative part runs first, and the variables and subprograms defined in the declarative part exist in the timing point sections to which the triggering statement applies.
A compound DML trigger contains at least one timing point section, as shown in the following table. Multiple timing point sections in a compound DML trigger can be sorted in any order, but the timing points cannot be duplicate.
Timing point |
Section |
|---|---|
| Before the triggering statement runs | BEFORE STATEMENT |
| After the triggering statement runs | AFTER STATEMENT |
| Before each row affected by the triggering statement | BEFORE EACH ROW |
| After each row affected by the triggering statement | AFTER EACH ROW |
A compound DML trigger has no initialization part. However, BEFORE STATEMENT can run prior to other timing point sections and therefore can be used for initialization. If a compound DML trigger has neither the BEFORE STATEMENT part nor the AFTER STATEMENT part and its triggering statement does not affect any rows, this compound trigger will never fire.
Limitations on compound DML triggers
Compound DML triggers are subject to the following limitations:
OLD,NEW, andPARENTcannot appear in the declarative part,BEFORE STATEMENTpart, orAFTER STATEMENTpart.- Only the
BEFORE EACH ROWpart can change the value ofNEW. - One timing point section cannot handle the exceptions raised in another timing point section.
- If a timing point section contains the
GOTOstatement, the destination of theGOTOstatement must be in the same timing point section.
Advantages of compound DML triggers
You can use a compound DML trigger with bulk SQL statements or when the triggering statement affects many rows, to improve performance.
In the following example, the triggering statement fires a compound DML trigger that contains four timing point sections. The BEFORE EACH ROW and AFTER EACH ROW parts of the trigger run for each row in the tbl1 table where the col1 column is greater than the col2 column. However, the BEFORE STATEMENT part runs only before the INSERT statement, and the AFTER STATEMENT part runs only after the INSERT statement.
INSERT INTO tbl2
SELECT col1
FROM tbl1
WHERE tbl1.col1 > col2;
In addition, you can use a compound DML trigger with bulk INSERT statements. The compound DML trigger accumulates rows and then sends them to other tables, to insert data in batches on a regular basis. You can also use compound DML triggers to avoid mutating-table errors with the error code of ORA-04091.
Examples
Use a compound trigger to record the changes in the child table of a table.
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 point section:
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 point section:
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 of the employee 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