Thursday, November 19, 2009

composable DML for Auditing

You can achieve this by using composable DML. You write an UPDATE statement with an OUTPUT clause, and define a derived table based on the UPDATE statement. You write an INSERT SELECT statement that queries the derived table, filtering only the subset of rows that is needed. Here's the complete solution code:
INSERT INTO dbo.ProductsAudit(productid, colname, oldval, newval)
SELECT productid, N'unitprice', oldval, newval
FROM (UPDATE dbo.Products
SET unitprice *= 1.15
OUTPUT
inserted.productid,
deleted.unitprice AS oldval,
inserted.unitprice AS newval
WHERE SupplierID = 1) AS D
WHERE oldval <>= 20.0;

No comments: