SQL Tips by Namwar Rizvi

May 10, 2007

Use OUTPUT to log what is happening to data during INSERT,UPDATE and DELETE

Filed under: Uncategorized — namwar @ 8:20 PM

Triggers are good…triggers are Bad, you can have your own opinion about their usage but one thing is the fact that RDBMS is not complete without them. They are one of the most common strategies to implement history logs to history tables to record old data values before modification or deletion.

There are some scenarios where you cannot use triggers simply because of DBA policies or other strategic or architectural constraints. For example, if you just want to record only those record values which are getting modified by an specific stored procedure, or you want to record values from table variable update statements.

For all these scenarios, here is the solution. Use new OUTPUT construct in SQL Server 2005 TSQL.

Following is the working example to show how you can use OUTPUT in update statement:

Set Nocount On
Declare
@m_tempTable table (itemId int, itemName varchar(50))
Declare @m_tempLogTable table (OlditemId int, NewItemId int,OlditemName varchar(50),NewitemName varchar(50))

—Adding a row into table
Insert into @m_tempTable values (1,‘BeforeUpdateItem’)

— First Update Statement —-

Update @m_tempTable
Set ItemName=‘ItemAfterFirstUpdate’
OutPut Deleted.ItemId,Inserted.ItemId,Deleted.itemName,Inserted.itemName
Into @m_tempLogTable

— Second Update Statement —-
Update @m_tempTable
Set ItemName=‘ItemAfterSecondUpdate’
OutPut Deleted.ItemId,Inserted.ItemId,Deleted.itemName,Inserted.itemName
Into @m_tempLogTable

–Display the data of log table recording after two updates
Select * from @m_tempLogTable

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: