Question

You will create the following 4 triggers:

- trgEmployee: Will be placed on the Employee table and listens for Inserts, Deletes, and Updates

- trgJob: Will be placed on the Job table and listens for Inserts, Deletes, and Updates -

trgProject: Will be placed on the Project table that contains the projectId and projectName.

- trgActivity: Will be placed on the Activity table that contains the activityId and activityName.

Again, each trigger will write to its respective audit table:

trgProject will write to ProjectAudit

trgActivity will write to ActivityAudit

trgEmployee will write to EmployeeAudit

trgJob will write to JobAudit

Again, the columns  which will be written to the audit tables will be all the original columns plus “Operation” and “DateTimeStamp”

MY CODE SO FAR:

create table EmployeeAudit
(
empNumber char(8) PRIMARY KEY,
firstName varchar(25),
lastName varchar(25),
ssn char(9),
address varchar(50),
state char(2),
CONSTRAINT EMP_STATECHECK CHECK (state IN('CA', 'FL')),
zip char(5),
jobCode char(4)
CONSTRAINT FK_JOB FOREIGN KEY (jobCode)
REFERENCES Job(jobCode),
dateOfBirth date,
certification bit,
salary money,
Operation (varchar(50)),
DateTimeStamp (datetime)
);go

create table JobAudit
(
jobCode(char(4) PRIMARY KEY,
CONSTRAINT JOB_JOBCODE CHECK(jobCode IN( 'CAST', 'ENGI', 'INSP', 'PMGR')),
jobdesc(varchar(50)),
Operation (varchar(50)),
DateTimeStamp (datetime)
);go

create table ProjectAudit
(
projectId char(4),
primary key(projectId),
projectName varchar(50),
firmFedID char(9),
fundedbudget decimal(16,2),
projectStartDate date,
projectstatus varchar(25),
projectTypeCode char(5),
projectedEndDate date,
projectManager char(8),
activityID char(4),
Operation (varchar(50)),
DateTimeStamp (datetime)
);go

create table ActivityAudit
(
activityId char(4),
projectId char(4),
activityName varchar(50),
costToDate decimal(16,2),
activityStatus varchar(25),
startDate date,
endDate date
primary key(projectId,activityId),
Operation (varchar(50)),
DateTimeStamp (datetime)
);go

create trigger trgEmployee
();go

create trigger trgJob
();go

create trigger trgProject
();go

create trigger trgActivity
();go

Answer 1

Creating Trigger Trigger_1:

CREATE TRIGGER trgProject_Insert ON ProjectAudit

FOR INSERT

AS

declare @projectID char(4),

declare @projectName VARCHAR (50),

declare @fundedBudget DECIMAL(16,2),

declare @firmFedID CHAR(9),

declare @startDate DATE,

declare @projectTypeCode CHAR(5),

declare @projectTypeDesc VARCHAR (50),

declare @projectStatus VARCHAR(25),

declare @projectEndDate DATE,

declare @projectManager CHAR(8)

insert into ProjectAudit values

(@projectID,@projectName,@fundedBudget,@firmFedID,@st

artDate,@projectTypeCode,@projectTypeDesc,@projectSta

tus,@projectEndDate,@projectManager);

PRINT 'AFTER INSERT trigger fired.'

CREATE TRIGGER Trg_ProjectAudit_Delete ON ProjectAudit

FOR DELETE

declare @projectID char(4)

select @projectID =d.projectManager from deleted d;

Delete from EmployeeAudit where empNumber=@projectID

Delete from ProjectAudit where projectManager=@projectID

PRINT 'Delete trigger fired.'

CREATE TRIGGER Trg_ProjectAudit_Update

ON ProjectAudit

AFTER UPDATE

AS

declare @projectID char(4)

SELECT @projectID = INSERTED.projectID   

FROM INSERTED

Update ProjectAudit Set projectName='Johns&Co' where

projectID=@projectID

Creating Trigger 2:

CREATE TRIGGER trgActivity_Insert ON ActivityAudit

FOR INSERT

AS

declare @activityID CHAR (4),

declare @activityName VARCHAR (50),

declare @activityTypeCode char (2),

declare @activityStatus VARCHAR (25),

declare @activityTypeDesc VARCHAR (50),

declare @costToDate DECIMAL (16,2),

declare @startDate DATE,

declare @endDate DATE,

declare @projectID char(4)

Insert into ActivityAudit values

(@activityID,@activityName,@activityTypeCode,@activit

yStatus,@activityTypeDesc,@costToDate,@startDate,@end

Date,@projectID)

CREATE TRIGGER trgActivity_Delete ON ActivityAudit

FOR DELETE

declare @projectID char(4)

select @projectID =d.projectID from deleted d;

Delete from EmployeeAudit where empNumber=@projectID

Delete from ProjectAudit where projectManager=@projectID

Delete from ActivityAudit where projectID=@projectID

CREATE TRIGGER trgActivity_Update

ON ActivityAudit

AFTER Update

AS

declare @activityID char(4)

SELECT @activityID = INSERTED.activityID   

FROM INSERTED

Update ActivityAudit Set activityName='Insert a record' where

activityID=@activityID

Creating Trigger 3:

CREATE TRIGGER trgEmployee_Insert ON EmployeeAudit

FOR INSERT

AS

declare @empNumber CHAR (8),

declare @SSN CHAR (9),

declare @firstName VARCHAR (25),

declare @lastName VARCHAR (25),

declare @address VARCHAR (50),

declare @state CHAR (2),

declare @zip CHAR (5),

declare @job VARCHAR (50)

Insert into EmployeeAudit values(@empNumber,@SSN,@firstName,@lastName,@address,@state,@zip,@job)

CREATE TRIGGER trgEmployee_Delete ON EmployeeAudit

FOR DELETE
As
declare @empNumber CHAR (8)

select @empNumber =d.empNumber from deleted d;

Delete from EmployeeAudit where empNumber=@empNumber

CREATE TRIGGER trgEmployee_Update ON EmployeeAudit

AFTER Update

declare @empNumber CHAR (8)

SELECT @empNumber= INSERTED.empNumber   

FROM INSERTED

Update EmployeeAudit Set firstName='Jhon' where empNumber=@empNumber

Creating Trigger 4:

CREATE TRIGGER trgJob_Insert ON JobAudit

FOR INSERT

AS

declare @jobCode(char(4),
declare @jobdesc(varchar(50)),
declare @Operation(varchar(50)),
declare @DateTimeStamp (datetime)

Insert into JobAudit values(@jobCode,@jobdesc,@Operation,@DateTimeStamp)

CREATE TRIGGER trgJob_Delete ON JobAudit

FOR DELETE

As

declare @jobCode(char(4))

select @jobCode =d.jobCode from deleted d;

Delete from JobAudit where jobCode=@jobCode

CREATE TRIGGER trgJob_Update ON EmployeeAudit

AFTER Update

declare @jobCode CHAR (4)

SELECT @jobCode= INSERTED.jobCode   

FROM INSERTED

Update JobAudit Set Operation='Create' where jobCode=@jobCode