SQLUSA

Microsoft SQL Server 2008 Best Practices

How to use the OUTPUT clause for audit trail?

 

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

 

 

 

 

SQLUSA - The Best SQL Server 2008 Training in the World
 
 
SQLUSA.com Home Page