Oracle Compound Triggers

Did you know that?

Since 11g, Oracle has something called “compound trigger”, that allows a single trigger to be used for combining actions for different timing points for a specific object.

The individual timing points share a single global declaration section, whose state is maintained for the lifetime of the statement. Once a statement ends, due to successful completion or an error, the trigger state is cleaned up. In previous releases this type of functionality was only possible by defining multiple triggers whose code and global variables were defined in a separate package.

The triggering actions are defined in the same way as any other DML trigger, with the addition of the COMPOUND TRIGGER clause. The main body of the trigger is made up of an optional global declaration section and one or more timing point sections, each of which may contain a local declaration section whose state is not maintained. For example:

CREATE OR REPLACE TRIGGER trigger_example
    FOR action ON table_name COMPOUND TRIGGER
    global_variable VARCHAR2(30);
    BEFORE STATEMENT IS BEGIN NULL; END BEFORE STATEMENT;
    BEFORE EACH ROW IS BEGIN NULL; END BEFORE EACH ROW;
    AFTER EACH ROW IS BEGIN NULL; END AFTER EACH ROW;
    AFTER STATEMENT IS BEGIN NULL; END AFTER STATEMENT;
END trigger_example;
/

A good summary with detailed information and examples can be found (as always) here: https://oracle-base.com/articles/11g/trigger-enhancements-11gr1

Cheers!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.