[PL/SQL: triggers (of the four types)] Write a script according to the following specifications.

i) Create a table, named audit_trail, for storing an audit trail record. It suffices to include one column of a variable length character string. Hint: feel free to use the same one used in a trigger example code in class.

ii) Create four types of triggers before_table, after_table, before_row, and after_row (and named as such, i.e.., before_table, after_table, before_row, and after_row) with the event defined on update of the salary column of the table Employee and the action specified as writing an audit record into the table created in step 1. Make each audit record as Before_table trigger fired., After_table trigger fired., Before_row trigger fired., and After_row trigger fired.

iii) Fire the triggers created in the step ii by executing an SQL update statement for raising the salaries of all employees by 10%. This single statement fires all four triggers.

iv) Show the content of the audit records by executing an SQL select statement.

v) Drop the audit_trail table and the four triggers.

vi) Restore the original salary values of the Employee tuples by executing an SQL update statement.

CREATE TABLE EMPLOYEE Figure 6.1 SOL CREATE TABLE data definition statements for defining the COMPANY schema from Fiqure 5.7 (Fname VARCHAR(15) CHAR, VARCHAR(15) CHAR(9) DATE, VARCHAR(30), CHAR, DECIMAL (10,2) CHAR(9), INT NOT NULL, Minit Lname Ssn Bdate Address NOT NULL, NOT NULL, ex Salary Super_ssn Dno NOT NULL PRIMARY KEY (Ssn), CREATE TABLE DEPARTMENT VARCHAR(15) INT CHAR(9) DATE, NOT NULL NOT NULL, NOT NULL name Dnumber Mgr ssn Mgr start_date PRIMARY KEY (Dnumber), UNIQUE (Dname) FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn)) CREATE TABLE DEPT LOCATIONS INT VARCHAR(15) NOT NULL NOT NULL (Dnumber Dlocation PRIMARY KEY (Dnumber, Dlocation) FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT (Dnumber)) CREATE TABLE PROJECT Pname NOT NULL NOT NULL Pnumber Plocation Dnum VARCHAR(15 INT VARCHAR(15), INT NOT NULL PRIMARY KEY (Pnumber) UNIQUE (Pname), FOREIGN KEY (Dnum) REFERENCES DEPARTMENT (Dnumber)); CREATE TABLE WORKS ON CHAR(9) INT DECIMAL(3,1) NOT NULL NOT NULL, NOT NULL ssn no Hours PRIMARY KEY (Essn, Pno), FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn) FOREIGN KEY (Pno) REFERENCES PROJECT (Pnumber)) CREATE TABLE DEPENDENT CHAR(9) VARCHAR(15 CHAR, DATE VARCHAR(8), NOT NULL NOT NULL ssn Dependent_name Sex Bdate Relationship PRIMARY KEY (Essn, Dependent_name), FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn)) CREATE TABLE EMPLOYEE Figure 6.1 SOL CREATE TABLE data definition statements for defining the COMPANY schema from Fiqure 5.7 (Fname VARCHAR(15) CHAR, VARCHAR(15) CHAR(9) DATE, VARCHAR(30), CHAR, DECIMAL (10,2) CHAR(9), INT NOT NULL, Minit Lname Ssn Bdate Address NOT NULL, NOT NULL, ex Salary Super_ssn Dno NOT NULL PRIMARY KEY (Ssn), CREATE TABLE DEPARTMENT VARCHAR(15) INT CHAR(9) DATE, NOT NULL NOT NULL, NOT NULL name Dnumber Mgr ssn Mgr start_date PRIMARY KEY (Dnumber), UNIQUE (Dname) FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn)) CREATE TABLE DEPT LOCATIONS INT VARCHAR(15) NOT NULL NOT NULL (Dnumber Dlocation PRIMARY KEY (Dnumber, Dlocation) FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT (Dnumber)) CREATE TABLE PROJECT Pname NOT NULL NOT NULL Pnumber Plocation Dnum VARCHAR(15 INT VARCHAR(15), INT NOT NULL PRIMARY KEY (Pnumber) UNIQUE (Pname), FOREIGN KEY (Dnum) REFERENCES DEPARTMENT (Dnumber)); CREATE TABLE WORKS ON CHAR(9) INT DECIMAL(3,1) NOT NULL NOT NULL, NOT NULL ssn no Hours PRIMARY KEY (Essn, Pno), FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn) FOREIGN KEY (Pno) REFERENCES PROJECT (Pnumber)) CREATE TABLE DEPENDENT CHAR(9) VARCHAR(15 CHAR, DATE VARCHAR(8), NOT NULL NOT NULL ssn Dependent_name Sex Bdate Relationship PRIMARY KEY (Essn, Dependent_name), FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn))