?> Create Combination insert, update, delete Trigger(Build-Execute)

Creating Triggers  «Prev  Next»

Lesson 6 Creating a combination insert, update, and delete trigger
Objective Build and execute a trigger that covers insert, update, and delete.

Creating a Combination insert, update, and delete Trigger in Oracle

You have now created several triggers that fire on different actions for the same table. In this lesson, you will learn to combine these three triggers into a single trigger. The advantage of combining the triggers into a single trigger is that they can share common code easily. For example, in the three triggers created during the exercise in the previous lesson, the exception-handling portion for the update and delete triggers is almost identical.
Looking back at the syntax for a trigger (repeated here for your convenience), you can see that it is possible to tell a trigger to fire on more than one action.

Syntax for a trigger consisting of 1) timing 2) trigger name 3) action 4) trigger assignment 
CREATE OR REPLACE TRIGGER PRE_CUSTOMER
BEFORE 
INSERT OR UPDATE ON CUSTOMER
FOR EACH ROW
BEGIN
   ... pl/sql statements
END

Syntax for a trigger consisting of 1) timing 2) trigger name 3) action 4) trigger assignment

Once you have the trigger parameters correct, the only other trick is to divide the body of the trigger up so that some portions are skipped and some are not skipped, depending on the action that fired the trigger. There are three functions built into Oracle that can be used with triggers to handle this:
  1. UPDATING. This function returns true when the action that fired the trigger is an update. Otherwise, it returns false.
  2. INSERTING. This function returns true when the action that fired the trigger is an update. Otherwise, it returns false.
  3. DELETING. This function returns true when the action that fired the trigger is an update. Otherwise, it returns false.
Click on the link below to learn how easy it is to combine triggers.
Combining Triggers
The next lesson wraps up this module.

Creating Trigger Combination - Exercise


Now it is time to try this yourself. Click the exercise link below to do this on your own.
Creating Trigger Combination - Exercise