Some trigger logic needs to act at several moments of one statement: read something before any row changes, look at each row, and do something once afterward. Separate triggers cannot share variables, so this used to need a package to hold state between them. A compound trigger puts all the timing points in one trigger, with variables they share for the duration of the statement.
Code for This Guide
The main examples are in the examples/triggers folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
create [or replace] trigger name
for {insert | update [of column] | delete} [or ...] on table
compound trigger
-- declarations shared by all timing points
before statement is begin ... end before statement;
before each row is begin ... end before each row;
after each row is begin ... end after each row;
after statement is begin ... end after statement;
end name;Any subset of the four timing points can be present. The shared variables are initialized when the statement starts and discarded when it ends.
Read Once, Check Each Row
The BEFORE STATEMENT section reads the highest salary once; the BEFORE EACH ROW section checks each new salary against it. This is also the standard fix for the mutating table error.
Example:
create or replace trigger employees_cap_trg
for update of salary on employees
compound trigger
v_max number;
before statement is
begin
-- read once, before the rows change
select max(salary) into v_max from employees;
end before statement;
before each row is
begin
if :new.salary > v_max then
raise_application_error(-20101, 'Salary above ' || v_max);
end if;
end before each row;
end employees_cap_trg;
/
update employees set salary = salary + 1 where employee_id = 161;
update employees set salary = 50000 where employee_id = 162;
rollback;Output:
Trigger EMPLOYEES_CAP_TRG compiled 1 row updated. Error starting at line : 21 In command - update employees set salary = 50000 where employee_id = 162 Error at Command Line : 21 Column : 8 Error report - SQL Error: ORA-20101: Salary above 48000 ORA-06512: at "NIMBUS.EMPLOYEES_CAP_TRG", line 13 ORA-04088: error during execution of trigger 'NIMBUS.EMPLOYEES_CAP_TRG' Rollback complete.
The second update exceeds the maximum and is refused with the trigger's error. The example rolls back and drops the trigger.
Count Rows, Report Once
AFTER EACH ROW collects information, and AFTER STATEMENT acts on it once, a pattern often used to write audit rows in bulk.
Example:
-- one trigger, several timing points, sharing a variable
create or replace trigger employees_count_trg
for update of salary on employees
compound trigger
v_rows pls_integer := 0;
after each row is
begin
v_rows := v_rows + 1;
end after each row;
after statement is
begin
dbms_output.put_line(v_rows || ' salaries changed in this statement');
end after statement;
end employees_count_trg;
/
update employees set salary = salary + 1 where department_id in (50, 60);
rollback;Output:
Trigger EMPLOYEES_COUNT_TRG compiled 7 salaries changed in this statement 7 rows updated. Rollback complete.
The trigger counts the seven changed rows and reports them once at the end of the statement. The example rolls back and drops its trigger.
Things to Know
- A compound trigger is for DML on a table or, with INSTEAD OF EACH ROW, on a view.
- Collecting rows in a collection in AFTER EACH ROW and inserting them with FORALL in AFTER STATEMENT is much faster than inserting row by row.
- :OLD and :NEW are available only in the row-level sections.
Related Guides
- How to Fix the Mutating Table Error in Oracle (ORA-04091)
- How to Write Statement-Level Triggers in Oracle
Conclusion
Compound triggers combine before-statement, row-level, and after-statement logic in one trigger with shared variables. Use them to read data once before rows change, to collect information per row and act on it once, and to fix mutating table errors.
