Create a trigger named TempEmpData_INSERT_UPDATE_DELETE. For this script you need to create 2 copy tables- TempEmpData (based on EmpData) and TempWork (based on Work). Test if the 2 copy tables are not null, drop and
recreate them.
The purpose of this trigger will be to prevent deletions of employees from the TempEmpData table if they have a corresponding record in the TempWork table and to make sure new employees are not inadvertently given an insurance plan
(ID 0 is a 90 day probation period listing)
Create the trigger on the TempEmpData table to fire after inserts, updates and
deletes.
Test for the existence of the EmpID in the TempWork table on any deletes. If the
EmpID exists, create a RAISERROR message ("EmpID is in use; transaction
cancelled!") with a severity level of 11 and a state code of 1. Roll back the
transaction after calling the RAISERROR.
If the EmpID does not exist, Update TempEmpData by setting the BenPlanID to
0 for new record inserts.
Test the trigger for proper functionality by running the following queries:
DELETE TempEmpData
WHERE EmpID = 41 (Qry5- Test1 result set)
INSERT TempEmpData
VALUES ('Bill', 'Smith', '11/14/2014', 0, 0, 1, NULL, 2)
SELECT * FROM TempEmpData
ORDER BY EmpID DESC (Qry5- Test2 result set)

 

Skeleton Code

 

--Test for the existance of a fake table named TempEmpData. If it exists, drop it.
--Test for the existance of a fake table named TempWork. If it exists, drop it.
--Select everything from EmpData into the appropriate fake table
--Select everything from Work into the appropriate fake table
 

CREATE TRIGGER TempEmpDate_INSERT_UPDATE_DELETE
ON TempEmpData

 

AFTER INSERT, UPDATE, DELETE
AS
--(USE THIS CONDITIONAL STRUCTURE- substitute variable names where needed and remove the "--")
--IF EXISTS (select everything the system table that handles deletes- join it to the fake work table on
--the correct primary key)
BEGIN
--Custom error message
--rollback the transaction
END
ELSE
BEGIN
--Update the appropriate fake table
--Set the appropriate column to the correct value
--Where the correct primary key is in a subquery selecting that same key from the
--system table that handles inserts
END