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 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to audit data changes with the OUTPUT clause?

Execute the following T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to demonstrate the use of OUTPUT clause with the UPDATE statement to create an audit trail of product list price changes.

-- OUTPUT quick syntax - Trigger alternative - inserted & deleted tables

INSERT Country

OUTPUT inserted.CountryID, inserted.Name INTO @InsertLog

VALUES(111,'Greece')

------------

USE AdventureWorks2008;
GO
-- Create audit table - mmsql create table
CREATE TABLE Production.ProductAudit (
      AuditDate datetime,
      ProductID int,
      ChangedColumn sysname,
      OldPrice money,
      NewPrice money
)
GO
 
-- T-SQL update with output clause - insert change info into audit table
UPDATE Production.Product
SET    ListPrice = ListPrice * 1.1
      OUTPUT getdate(), convert(VARCHAR,DELETED.ProductID),
            'ListPrice', convert(VARCHAR,DELETED.ListPrice),
            convert(VARCHAR,INSERTED.ListPrice)
      INTO Production.ProductAudit
WHERE  ListPrice > 0.0 and ListPrice < 100.0;
 
SELECT * FROM Production.ProductAudit
GO
/* Partial results
 
AuditDate               ProductID   ChangedColumn     OldPrice    NewPrice
2009-03-15 04:33:20.373 707         ListPrice         34.99       38.49
2009-03-15 04:33:20.373 708         ListPrice         34.99       38.49
2009-03-15 04:33:20.373 709         ListPrice         9.50        10.45
2009-03-15 04:33:20.373 710         ListPrice         9.50        10.45
2009-03-15 04:33:20.373 711         ListPrice         34.99       38.49
*/
 
-- Cleanup
-- Undo price increase
UPDATE Production.Product
SET    ListPrice = ListPrice / 1.1
      OUTPUT getdate(), convert(VARCHAR,DELETED.ProductID),
            'ListPrice', convert(VARCHAR,DELETED.ListPrice),
            convert(VARCHAR,INSERTED.ListPrice)
      INTO Production.ProductAudit
WHERE  ListPrice > 0.0 and ListPrice < 100.0 * 1.1; 
GO
DROP TABLE Production.ProductAudit

GO

------------
 
 

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