-- DATABASE SYSTEMS 2020
-- LAB EXERCISE week 8
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- Triggers
-- ~~~~~~~~~~~~~~~~~~~~

-- DreamHome has some suspicions that someone is changing staff salaries just before payday. 
-- Create an audit table to log changes being made to the staff table with the following fields:
-- staffNo, positionBefore, positionAfter, salaryBefore, salaryAfter, user responsible and the date and time of the change (NO NEED FOR ANY CONSTRAINTS)




GO
-- Create a DML Trigger that responds to UPDATES ONLY on the Staff table and logs the required data items in the newly created StaffAudit table



GO
SELECT * FROM Staff;

-- Perform a salary update to a staff member of your choice
-- WARNING: be careful not to update all salaries :)

-- Did your staff audit table get updated?


-- ~~~~~~~~~~~~~~~~~~~~

-- Add a new staff record to the Staff table?
-- Did it add a new row to the StaffAudit table? If not, why? And if yes, what did it add?



GO
-- ~~~~~~~~~~~~~~~~~~~~

-- Modify the Trigger you created to respond to ALL CHANGES (UPDATEs, INSERTs, DELETEs) made to the Staff Table
-- NOTE: You will need to DROP the Trigger and then recreate it.



GO

-- Test your new trigger by:
-- a. adding a new record
-- b. deleting the staff member you added previously 


