DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to use the OUTPUT clause for audit trail?

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

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE