13.3 How do I…Fire triggers selectively using trigger predicates?
Problem
We have a lot of tables in the database. Instead of writing three different triggers for INSERT, UPDATE, and DELETE operations, I want to be write one trigger for each table in the system, and that trigger should handle any DML operations on that table. I need to know how to create such triggers and in the trigger body how do I recognize the type of DML operation that caused the trigger to fire. How do I fire triggers selectively using trigger predicates?
Technique
You can write a single trigger to handle multiple DML operations on a table. For instance, an INSERT, DELETE, or UPDATE statement can fire the same trigger with the use of the ON INSERT OR DELETE OR UPDATE OF clause while creating the trigger. The trigger body can use the conditional predicates INSERTING, DELETING, and UPDATING to execute specific blocks of code, depending upon the triggering statement.
Steps
1. Run SQL*Plus and connect as the WAITE user account. If you do not have the DEPT13 table in your schema, run CHP13_10.SQL from Step 1 of the previous How-To to create the table. In How-To 13.1, a separate trigger was created to handle an INSERT, UPDATE, and DELETE event. The steps presented in this How-To will create a trigger to handle a combination of these events.
2. Run the CHP13_13.SQL file in SQL*Plus, as shown in Figure 13.13. The CREATE TRIGGER statement in the file creates an all-in-one BEFORE INSERT, BEFORE UPDATE, and BEFORE DELETE trigger on the DEPT13 table.
Line 1 presents the keywords required to create the new trigger and names it as DEPT_ALL_13. Line 2 specifies that the trigger should be fired every time the DEPT13 table is manipulated by executing an INSERT, UPDATE, or DELETE statement. The FOR EACH ROW clause causes the trigger to be executed for each modified row. If it were not included in the statement, the trigger would only fire once for the statement, irrespective of the number of records updated or deleted. Line 5 uses the INSERTING trigger predicate to check if it is an INSERT statement being issued. Line 7 checks for an UPDATE operation and line 9 checks for a DELETE operation on the DEPT13 table.
Any modifications to the DEPT13 table will fire the trigger and the output displayed in SQL*Plus will correspond with the triggering statement.
3. Run the CHP13_14.SQL file is SQL*Plus, as shown in Figure 13.14. The INSERT statement contained in the file inserts a record in the DEPT13 table, causing the trigger to fire.
4. If you have not already created the DEPT_BUR_13 trigger in your schema by running the CHP13_11.SQL file from the previous How-To, you can run this file now to see the effects of multiple triggers on a table. Run the CHP13_15.SQL file is SQL*Plus (see Figure 13.15. The UPDATE statement contained in the file modifies both records in the DEPT13 table, causing the trigger to fire twice once for each row that is affected.
The output displayed shows the output from both triggers, the trigger created in Step 2 and the trigger created by CHP13_11.SQL are fired for each updated row of the DEPT13 table. When the trigger created in Step 2 is executed, the UPDATING predicate in the IF statement evaluates to TRUE causing the corresponding message to be displayed in SQL*Plus. Note that triggers of different types are fired in a specific order, but triggers of the same type for the same triggering statement are not guaranteed to fire in a specific order.
How It Works
Instead of creating a separate trigger for INSERT, UPDATE, and DELETE, you can use trigger predicates in a trigger that will fire whenever a DML statement operates on a table. Step 1 creates the sample table and data. Step 2 creates a trigger that will fire before a row is manipulated in the DEPT13 table with an INSERT, UPDATE, or DELETE statement. Note the use of BEFORE INSERT OR UPDATE OR DELETE ON DEPT13 as the triggering event clause. The trigger uses the INSERTING, UPDATING, and DELETING conditional predicates in an IF statement to determine the type of DML statement that caused the trigger to fire. You can further refine the UPDATING predicate to include a column name, for example, by specifying IF UPDATING (‘DEPT_NAME’) THEN would cause the conditional predicate to evaluate to TRUE only if the DEPT_NAME column in the DEPT13 table is updated. In Step 3, an INSERT statement is executed causing the BEFORE INSERT trigger to fire. Step 4 updates the two records in the DEPT13 table, causing the BEFORE UPDATE trigger to be fired twice. If a DELETE statement was run the effects would be similar to the UPDATE statement, here the BEFORE DELETE statement would be fired for each row deleted from the table.
Comments
A trigger on a table that handles multiple DML operations can use conditional predicates INSERTING, UPDATING, and DELETING in the trigger body to determine the DML statement that fired the trigger. Separate actions can be taken in the trigger body depending on the type of DML operation that took place.