oracle plsql compound trigger - ghdrako/doc_snipets GitHub Wiki

Oracle Database can fire triggers at four different points during the execution of the statement.

• Before the firing statement
• Before each row that the firing statement affects
• After each row that the firing statement affects
• After the firing statement Different triggers can be built for each DML action: insert, update, and delete.

The concept of compound triggers was introduced in Oracle Database 11g. All the actions can be built into a single trigger with a compound trigger.

Compound trigger allows sharing of state between all the trigger points using variables. The compound trigger has a declaration section, similar to the declaration section of a package. The common state is created at the start of the triggering statement and is destroyed at the completion of the trigger.

create or replace trigger compound_trigger_name
for [insert|delete]update] [of column] on table
compound trigger
-- declarative section (optional)
-- variables declared here have firing-statement duration.
--executed before dml statement
before statement is
begin
null;
end before statement;
--executed before each row change- :new, :old are available
before each row is
begin
null;
end before each row;
--executed after each row change- :new, :old are available
after each row is
begin
null;
end after each row;
--executed after dml statement
after statement is
begin
null;
end after statement;
end compound_trigger_name;
/

Common use cases:

Journaling

Instead of inserting a row in the journal table after a row has been processed, you can accumulate all the data of the rows affected in collections in the compound trigger and then use bulk operations to push this data to the journaling table.