Call multiple trigger bodies with a single trigger
As applications become more and more complex, supporting business rules with triggers becomes more difficult.
Triggers are useful for a wide range of applications, such as auditing, complex data validation, snapshots, and data generation.
The problem is, within a single schema, a table or view can only have triggers of each of the four main types defined: before row, after row, before statement, and after statement.
To avoid this kind of conflict, it's useful to define a standard pattern for trigger creation, which allows multiple PL/SQL or Java bodies to attach to trigger events easily.
The first step is to resist the temptation to follow Oracle documentation exactly, which usually places application logic directly inside a trigger. Use the trigger body to explicitly call another stored procedure, which packages multiple application procedure calls together. It would have been a very easy process if you could write a trigger such as Listing A.
Unfortunately, even though ":OLD" and ":NEW" look syntactically like ROWTYPE variables, they're actually bound to something outside the scope of PL/SQL. You can only reference them through their ":OLD.COLUMN" syntax. You cannot avoid either declaring each column as a parameter, or declaring and populating a ROWTYPE variable. View Listing B.
It can be drudgery to code these triggers one at a time and maintain them when columns change on a table. Listing C uses a stored procedure that automatically generates the above code base on a table's columns.
Notice that, since PL/SQL doesn't respect ROLE privileges, the user running the above code needs "CREATE ANY TRIGGER" privilege granted directly (GRANT CREATE ANY TRIGGER TO SCOTT) for the procedure to work correctly. Now, to show the code in action, Listing D creates a table and stored procedures, builds a trigger that calls the collection of stored procedures, and shows its effect on insertion into the table.
This shows how you can maintain independent functionalities, such as application-level security, auditing, logging, or snapshots, on the same table for the same events without merging application code. This is just a rough example; in reality, you may wish to restrict which columns get copied into the ROWTYPE and back.
Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development.