rigger and Stored Procedure:

$1.$ Write Triggers that logs any changes on Salary column of Employee table.

Trig$\_$Update$\_$Audit$\_$EmpSalary to log any changes of salary by Update

Trig$\_$Insert$\_$Audit$\_$EmpSalary to log any changes of salary by Insert

Trig$\_$Delete$\_$Audit$\_$EmpSalary to log any changes of salary by Delete

$1)$ Before creating any trigger for this lab, Alter Table to Drop all the PK$,$ FK$,$

Unique Constraints, Cascade, Check options from the Tables Employee

and Department for this lab to avoid any possible conflict with a system

trigger or any table mutating problem.

Or you can create a new Employee table without any Constraints for Lab$6.$

$3)$ Write $($Create$)$ Stored Procedure SP$\_$Audit$\_$ EmpSalary that inserts all the

history of the data of changes by Update, Insert, Delete on Salary of Employee

Table to Audit$\_$EmpSalary table.

$4)$ Call the Stored procedure SP$\_$Audit$\_$EmpSalary at the end of the body of

each Trigger to record all the history of the changes by Update$/$Insert$/$Delete$.$

$2.$ Write Stored Procedure SP$\_$Audit$\_$ EmpSalary as follow. Whenever data for

the salary column in Employee table are updated, deleted, or inserted, both

the previous and new salary values are recorded in Audit$\_$EmpSalary table to

allow tracing the history of changes.

$1)$ On Update of the trigger, Insert the new record into a table named

Audit$\_$EmpSalary as follow:

$($date$\_$of$\_$change, DML$\_$Type, old$\_$SSN$,$ new$\_$SSN$,$ old$\_$Salary,

new$\_$Salary, old$\_$Dno, new$\_$Dno$)$

$2)$ On Delete of the trigger, Insert the deleted record into the table

Audit$\_$EmpSalary as well. Since there is no new record for delete,

insert NULL for the new record columns.

$3)$ On Insert of the trigger, Insert the newly inserted record into a table

named Audit$\_$ EmpSalary as well. Since there is no old record for

Insert, insert NULL for the old record columns.

$3.$ Let each Event happen to get each trigger you created fired by executing

the following DML on Employee table One at a time:

UPDATE EMPLOYEE with Salary to $99,000$ for Employees of DNO $=5$

UPDATE Employee SET Salary $=99000$ WHERE Dno $=5$;

$($Submit Update statement to change salary to $99,000$ to activate your

Trig$\_$Update$\_$Audit$\_$EmpSalary$)$

DELETE John Smith from EMPLOYEE

$($Submit Delete statement to activate your Trig$\_$Delete$\_$Audit$\_$EmpSalary$)$

INSERT EMPLOYEE with Your Info

$($Submit Insert statement with your info to activate your

Trig$\_$Insert$\_$Audit$\_$EmpSalary$)$

$4.$ Show each trigger you create was fired correctly with Select statements on

affected columns and tables Before and After the Trigger is fired $.$

$//$ You don$$t have to follow all the steps in this part $5$ for testing unless you

need to$.$ It is just suggestion $.$

$5.$ Do not make all the DML events above in task $3$ at once to fire all the

triggers. Make one event per a Trigger creation per one transaction to test to

avoid any possible concurrent table mutation problem. This means that your

script or a query execution sequence should look like in the following order:

Login $//$if needed $--$ optional

$1.$ Create or replace Trigger$\_$Update on Update

$2.$ Select statement to show the original table before the Trigger$\_$Update

fired

$3.$ One Update statement to trigger$($fire$)$ the Trigger$\_$Update

$4.$ Select statement to show the tables affected by Trigger$\_$Update

$5.$ Drop Trigger$\_$Update $//$optional

Logout $//$optional

Login $//$optional

$1$T$.$ Create Trigger$\_$Delete on Delete

$2.$ Select statement to show the original table before the Trigger$\_$Delete

fired

$3.$ One Delete statement to trigger the Trigger$\_$Delete

$4.$ Select statement to show the table affected by Trigger$\_$Delete

$5.$ Drop Trigger$\_$Delete $//$optional

Logout $//$optional

$5.$ Submit Screenshots of each trigger creation, triggering event, and Select on

affected columns and tables to show that your triggers all get correctly fired

as well as the data inserted in Audit$\_$ EmpSalary by the Stored Procedure

called at the end of each trigger.