Lab 9: PL/SQL Trigger Programming


After completing this lab you should be able to:

Due for Lab 9

Turn in the PL/SQL source code for your trigger. In addition to this, update and insert some EMPLOYEE rows, then select the data generated in the EMPLOYEE_LOG table to show that your EMPLOYEE_TRIGGER works. Capture this work in a spool file and then turn these in.

Lab Assignment Steps

In this lab, you are to create a log table that will record all changes that occur to your EMPLOYEE table from lab 5.

  1. create an EMPLOYEE_LOG table with columns as shown below:
    SQL> desc employee_log
     Name                            Null?    Type
     ------------------------------- -------- ----
     EMPLOYEE_ID                     NOT NULL VARCHAR2(8)
     PREV_LAST_NAME                           VARCHAR2(40)
     PREV_FIRST_NAME                          VARCHAR2(40)
     PREV_MIDDLE_INITIAL                      VARCHAR2(1)
     PREV_DEPT_ID                             VARCHAR2(4)
     PREV_TITLE                               VARCHAR2(40)
     PREV_SUPERVISOR                          VARCHAR2(8)
     MOD_USER                                 VARCHAR2(8)
     MOD_TIMESTAMP                            DATE

  2. Next, create a PL/SQL trigger called EMPLOYEE_TRIGGER on the EMPLOYEE table. This trigger will be executed after every insert or update.