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

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