Execute the following script in Query Editor to create an audit log table and populate it with an OUTPUT clause when salary hourly rate changes for employees.

USE AdventureWorks2008;

-- drop table HumanResources.Audit

CREATE TABLE HumanResources.Audit

( AuditID int identity(1,1) primary key,

ChangeDate datetime,

EmployeeID int,

ChangeItem varchar(64),

PreviousValue varchar(64),

CurrentValue varchar(64)

)

GO

-- select * from HumanResources.EmployeePayHistory

UPDATE HumanResources.EmployeePayHistory

SET Rate = Rate * 1.1

OUTPUT getdate(), convert(varchar,DELETED.BusinessEntityID), 'Hourly Rate',

convert(varchar,DELETED.Rate), convert(varchar,INSERTED.Rate)

INTO HumanResources.Audit

(ChangeDate, EmployeeID, ChangeItem, PreviousValue, CurrentValue)

WHERE BusinessEntityID = 173 ;

GO

SELECT * FROM HumanResources.Audit

GO