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

-- OUTPUT clause for auditing INSERT, UPDATE, DELETE, or MERGE statement

USE AdventureWorks2008;

-- T-SQL create audit trail table for Human Resources

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

-- Increase pay rate 10% for employee 173

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

-- Reverse the change since this is just a demo

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

/*

AuditID     ChangeDate              EmployeeID  ChangeItem  PreviousValue     CurrentValue

1           2009-12-21 14:35:02.690 173   Hourly Rate 9.50  10.45

2           2009-12-21 14:35:02.760 173   Hourly Rate 10.45 9.50

*/

DROP TABLE HumanResources.Audit

GO

Related articles:

OUTPUT Clause (Transact-SQL)

Trigger Alternatives in SQL Server - OUTPUT Clause