After completing this lab you should be able to:

• Understand the basic syntax of the PL/SQL programming language.
• Understand what a trigger does.
• Write a trigger to generate an audit trail of changes.

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.

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.
   
   
   • For inserts, this trigger should put the new EMPLOYEE.EMPLOYEE_ID value, the user in MOD_USER, and the system date in MOD_TIMESTAMP to record the creation of the data into EMPLOYEE_LOG.
     
     
   • For updates, this trigger should put the previous EMPLOYEE values into the corresponding EMPLOYEE_LOG column as well as recording the MOD_USER and MOD_TIMESTAMP.