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.

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:

UPDATING. This function returns true when the action that fired the trigger is an update. Otherwise, it returns false.
INSERTING. This function returns true when the action that fired the trigger is an update. Otherwise, it returns false.
DELETING. This function returns true when the action that fired the trigger is an update. Otherwise, it returns false.

to combine triggers. The next lesson wraps up this module.

Combining Triggers in Oracle

You have seen two triggers for the PRODUCT table in previous simulations. One trigger fired on updates and the other fired on inserts. You will use the content of the two triggers to create a single trigger that fires on both inserts and updates of the PRODUCT table. The first step is to type in the first line of the CREATE command. Here is where you name the trigger. The name reflects the function of the trigger, before update and insert of the PRODUCT table.
Type: CREATE OR REPLACE TRIGGER PRE_UPDINS_PROD
The next line specifies what action on which table fires the trigger. In this case, instead of one action, we are specifying two actions. Type: BEFORE INSERT OR UPDATE ON PRODUCT
The next line specifies that the trigger fires on each row, rather than once for each group of rows in a transaction. Type: FOR EACH ROW
I have filled in line 4 for you (the word DECLARE). Now you will define a variable that will be used to store an error message. This is needed, as you'll see in later steps, because we are sharing an exception handler and yet we want a different error message when inserting than when updating. To define the message holder, type: V_MSG VARCHAR2(60);
Now you must declare the user-defined exception needed. In this example, the exception is called PRICE_TOO_LOW. To declare it type: PRICE_TOO_LOW EXCEPTION;
I have added the BEGIN line for you. The next step is to define the body of the trigger. Since we have two different actions firing the trigger, we need to decide which parts of the bodies of the two original triggers are shared and which parts should be used for only the updates and which parts should be used for only the inserts. Let's begin with the part that should only happen on an update. Type the following IF command to tell the trigger that this part of the body belongs with the update action: IF UPDATING THEN
I have added the contents of the IF statement for you. Looking at the code, you see that you are comparing the old sale price (from the row before it is updated), to the new sale price (from the update action that fired the trigger). If the new price is lower than the old price, a message is placed into the V_MSG variable and the PRICE_TOO_LOW exception is raised. Otherwise, the action continues. The next part of the trigger body fires for both inserts and updates. You type a comment marking the beginning of this part and I will fill in the rest of the code for you. Type: --inserting and updating.
Looking at the code, you can see another IF statement that checks to be sure that the sale price is less than the store cost. If so, a different error message is placed into the message variable and the PRICE_TOO_LOW exception is raised. Now it is time to define the exceptions. Type: EXCEPTION WHEN PRICE_TOO_LOW THEN
I have filled in the next line for you. Now, you must type in the parameters for the RAISE_APPLICATION_ERROR procedure. The first parameter is the return code, which is -20000. The second parameter is the error message, which is contained in the V_MSG variable. I also added text at the end of the error message that identifies the trigger by name (for easier debugging). Type: (-20000, V_MSG || ' (PRE_UPDINS_PROD)');
The last part of the trigger is a catch-all exception to handle any stray database errors that might occur. To start off the definition, type: WHEN OTHERS THEN
I have filled in the remaining code for the exception. Execute the command and create the trigger by typing a slash (/).
Well done, you created a trigger that fires on both inserts and updates of the PRODUCT table.

Perform commits and rollbacks in your database triggers

If you define a trigger as an autonomous transaction, then you can commit and/or roll back in that trigger, without affecting the transaction that fired the trigger. Why is this valuable? You may want to take an action in the database trigger that is not affected by the ultimate disposition of the transaction that caused the trigger to fire. For example, suppose that you want to keep track of each action against a table, whether or not the action completed. You might even want to be able to detect which actions failed.

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