Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

Tag Archives: DELETED

SQL Server offers an OUTPUT command to capture data that is changed, either through updating, deletion or insertion. The ‘before’ and ‘after’ images of the data are stored in system tables named DELETED and INSERTED. It is possible to query the applicable table to retrieve data that has been deleted or inserted by your sql statement. In the case of updates, the old data can be found in DELETED and the newly written data in INSERTED.

The following example demonstrates the process. An Audit table is created to store the results. An UPDATE is then issued against a number of columns. OUTPUT writes the old and new versions of the data (along with the id of the row changed and the date and time) to the audit table. Adding the output clause to stored procedures will allow you to audit all data changes in your database, although there will obviously be some overhead due to additional writes.

CREATE TABLE Audit_Table (CustomerID varchar(12),
                         OldAddress1 varchar(50),
                         OldAddress2 varchar(50),
                         OldAddress3 varchar(50),
                         OldAddress4 varchar(50),
                         OldAddress5 varchar(50),
                         OldPostCode varchar(12),
                         NewAddress1 varchar(50),
                         NewAddress2 varchar(50),
                         NewAddress3 varchar(50),
                         NewAddress4 varchar(50),
                         NewAddress5 varchar(50),
                         NewPostCode varchar(12),
                         DateChanged datetime);

UPDATE CustomerTable
SET Address1 = '99 My Road',
      Address2 = 'My Area',
      Address3 = 'My City',
      Address4 = 'My County',
      Address5 = 'My Country',
      PostCode = 'AA1 1AA'
	    DELETED.Address1 AS 'OldAddress1',
	    INSERTED.Address1 AS 'NewAddress1',
	    DELETED.Address2 AS 'OldAddress2',
	    INSERTED.Address2 AS 'NewAddress2',
	    DELETED.Address3 AS 'OldAddress3',
	    INSERTED.Address3 AS 'NewAddress3',
	    DELETED.Address4 AS 'OldAddress4',
	    INSERTED.Address4 AS 'NewAddress4',
	    DELETED.Address5 AS 'OldAddress5',
	    INSERTED.Address5 AS 'NewAddress5',
	    DELETED.stu_capc AS 'OldPostCode',
	    INSERTED.stu_capc AS 'NewPostCode',
	    GETDATE() AS 'DateChanged'
INTO AuditTable
WHERE CustomerTable.CustomerID = 'AB123456';

Tags: , , , , ,

%d bloggers like this: