Combining Triggers in Oracle

1. 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
2. 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
3. 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
4. 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);
5. 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;
6. 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
7. 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.
   
8. 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
9. 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)');
10. 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
11. I have filled in the remaining code for the exception. Execute the command and create the trigger by typing a slash (/).
12. 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