Compound triggers

This entry is part 8 of 8 in the series New Pl/Sql features 11g

If you put business rules into triggers validating your data your could easily grow a considerable forest around your tables. Making it hard to see for anyone exactly how all these triggers work together or even worse.. fight each other. Oracle recognized this and introduced the compound trigger. Basically it’s a special package… for triggers in which you can consolidate all your different triggers. What’s even better is that like packages these compound triggers can have a global declarations section. Again just like your packages.

A compound trigger on a table or editioning view has 1 to 4 timing points, which occur in the following order (if used of course)

  1. BEFORE STATEMENT; runs once before the statement that triggered the trigger runs
  2. BEFORE EACH ROW; runs before each row the statement that triggered the trigger will affect
  3. AFTER EACH ROW; runs after each row the statement that triggered the trigger has affected
  4. AFTER STATEMENT; runs once after the statement that triggered the trigger has run

A compound trigger on a non-editioning view has a single timing point

  1. INSTEAD OF EACH ROW

Any timing point section can include the functions

  • INSERTING
  • UPDATING
  • DELETING
  • APPLYING

An example of using a compound trigger to audit all updates on the salary in the EMP table

 

Series Navigation<< Predictable execution order of triggers

Posted in Oracle, Pl/Sql Tagged with: , , ,

Leave a Reply