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:
related post http://www.sqlhub.com/2011/07/audit-trail-with-output-clause-in-sql.html
Post a Comment