OUTPUT (Deleted and Inserted) from insert, delete and update statement in SQL-Server 2005 (kind of pseudo table of Trigger):

Monday, March 16, 2009 |

You may remember pseudo table in trigger from which we can get manipulated data. There was no way out to access that pseudo table outside the trigger before Microsoft SQL Server 2005. The same concept you can get outside of trigger as well in Microsoft SQL Server 2005.

Let us create one table for demonstration

--create first table for demonstration

CREATE TABLE CAR

(

ID int IDENTITY(1,1),

CarName VARCHAR(16),

CarDesc VARCHAR(50)

)

--insert records

Insert into Car (CarName,CarDesc) values ('Honda Pilot','SUV')

Insert into Car values ('Honda CRV','SUV')

Return data from INSERT statement with INSERTED table

Insert into Car

OUTPUT INSERTED.* --this statement will return all the field of CAR table

-- from INSERTED table

values ('Honda CRV','SUV')

GO

Return data from DELETE statement WITH DELETED table

DELETE FROM CAR

OUTPUT DELETED.* --this statement will return all records

--which are just deleted based on where condition

where ID=1
GO

Return data from UPDATE statement from INSERTED and DELETED table.

UPDATE CAR SET CarDesc='Luxury car'

OUTPUT DELETED.CarDesc as 'Old Value', INSERTED.CarDesc as 'New Value'

WHERE ID=2
GO

Enjoy the power of Microsoft SQL Server 2005

Happy SQLing!!!!!

Reference: Ritesh Shah

1 comments:

Ritesh Shah said...

related post http://www.sqlhub.com/2011/07/audit-trail-with-output-clause-in-sql.html