This exercise works with the Pet Store schema (our course project) and tests your new skills in using Oracle exclusive operators and conditions and your ability to create a hierarchical query. You will be creating three triggers that work on the PET_CARE_LOG table in the Petstore schema.

You want to improve and automate the House-o-Pets database system by adding some triggers that work with the pet care log. You want to automate the database so that it fills in the user and date when someone inserts a record into the table. You also want to allow users only to update records that they created. An update on someone else’s record will fail. And finally, you want to allow only the manager to delete records from the log. Anyone else's attempt to delete a record, even a record created by the user, will fail. The three triggers will be:

1. Create a trigger that fires before inserting each row in the PET_CARE_LOG table. The trigger will assign the current data and time to the UPDATE_DATE column. It will also assign the current user to the UPDATED_BY_USER column. Use pseudocolumns to get the values that you need. Handle all errors in one general exception handler and send an error message using the RAISE_APPLICATION_ERROR procedure.
2. Create a trigger that fires before updating each row of the PET_CARE_LOG table. This trigger will look at the current user and compare it with the value in the UPDATED_BY_USER column. If the two are the same, the update proceeds. If they are different, the update raises an exception and fails. Handle any other database errors the same way you did in the insert trigger.
3. Create a trigger that fires before any row is deleted from the PET_CARE_LOG table. This trigger looks at the user who is deleting the row. If the user is ‘JOEMANAGER,’ the delete continues successfully. Otherwise, the delete fails and sends an error message. Handle any other database errors the same way you did in the insert trigger.

Write the PL/SQL on your own, then cut and paste all three triggers into the text box below.
Click the Submit button to submit your answers.
Remember that you must submit all your responses to this exercise at once.