Compound Trigger Example in Oracle
The compound trigger is a new trigger that has been introduced in 11gR1. The compound trigger makes it easier to program one trigger to fire at various timing points instead of multiple simple triggers each fire at a single timing point.
To achieve the same effect with simple triggers, you had to model the common state with an ancillary package. This approach was both cumbersome to program and subject to a memory leak when the triggering statement caused an error and the after-statement trigger did not fire.
A compound trigger has a declaration section and a section for each of its timing points (see Example 9–2). All of these sections can access a common PL/SQL state. The common state is established when the triggering statement starts and is destroyed when the triggering statement completes, even when the triggering statement causes an error.
A compound trigger can fire at more than one timing point. Two common reasons to use compound triggers are:
- To accumulate rows destined for a second table so that you can periodically bulk-insert them.
- To avoid the mutating-table error (ORA-04091)
A compound trigger has a declaration section and at least one timing-point section. The declaration section (the first section) declares variables and subprograms that timing-point sections can use. When the trigger fires, the declaration section executes before any timing-point sections execute. Variables and subprograms declared in this section have firing-statement duration. A compound trigger defined on a view has an INSTEAD OF EACH ROW timing-point section and no other timing-point section.
Consider the below example:
CREATE TABLE employee_salaries ( employee_id NUMBER NOT NULL, change_date DATE NOT NULL, salary NUMBER(8,2) NOT NULL, CONSTRAINT pk_employee_salaries PRIMARY KEY (employee_id, change_date), CONSTRAINT fk_employee_salaries FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE) / CREATE OR REPLACE TRIGGER maintain_employee_salaries FOR UPDATE OF salary ON employees COMPOUND TRIGGER -- Declaration Section: -- Choose small threshhold value to show how example works: threshhold CONSTANT SIMPLE_INTEGER := 7; TYPE salaries_t IS TABLE OF employee_salaries%ROWTYPE INDEX BY SIMPLE_INTEGER; salaries salaries_t; idx SIMPLE_INTEGER := 0; PROCEDURE flush_array IS n CONSTANT SIMPLE_INTEGER := salaries.count(); BEGIN FORALL j IN 1..n INSERT INTO employee_salaries VALUES salaries(j); salaries.delete(); idx := 0; DBMS_OUTPUT.PUT_LINE('Flushed ' || n || ' rows'); END flush_array; -- AFTER EACH ROW Section: AFTER EACH ROW IS BEGIN idx := idx + 1; salaries(idx).employee_id := :NEW.employee_id; salaries(idx).change_date := SYSDATE(); salaries(idx).salary := :NEW.salary; IF idx >= threshhold THEN flush_array(); END IF; END AFTER EACH ROW; -- AFTER STATEMENT Section: AFTER STATEMENT IS BEGIN flush_array(); END AFTER STATEMENT; END maintain_employee_salaries; / /* Increase salary of every employee in department 50 by 10%: */ UPDATE employees SET salary = salary * 1.1 WHERE department_id = 50 / /* Wait two seconds: */ BEGIN DBMS_LOCK.SLEEP(2); END; / /* Increase salary of every employee in department 50 by 5%: */ UPDATE employees SET salary = salary * 1.05 WHERE department_id = 50 /