Visual studio feeds

All Visual Studio blogs in one place


Enter your email address:

Delivered by FeedBurner

Increase your website traffic with



Anti-spam: How many eyes has a typical person?

Follow us on FB


OUTPUT clause and triggers

AddThis Social Bookmark Button
Output clause has been added to SQL 2005 which can mainly used to grab the identity values. This has been really helpful addition to SQL 2005. You can get more information regarding output clause here. I have faced couple of incidents in last six months involving use of output clause and trigger which are not very well-known, at least I was not aware of those things. Case-1  If table on which we are performing DML operation has trigger for that action, it’s mandatory to use OUTPUT INTO for that DML operation. For example, use tempdb if OBJECT_ID('dbo.table1') is not null drop
table dbo.table1 go CREATE TABLE dbo.table1 ( id INT, employee VARCHAR(32) ) go INSERT INTO dbo.table1 VALUES (1, 'Fred') ,(2, 'Tom') ,(3, 'Sally') ,(4, 'Alice'); GO SELECT * FROM dbo.table1; DELETE FROM dbo.table1 OUTPUT DELETED.* WHERE id = 4 --So everything works fine here --But now, if we create a trigger for DELETE go CREATE TRIGGER tr_table1_delete on table1 after delete as select 1 GO DELETE FROM dbo.table1 OUTPUT DELETED.* WHERE id = 4 /* Here we ger below error: Msg 334, Level 16, State 1, Line 1 The target table 'dbo.table1' of the DML statement cannot...(Read whole news on source site)

Home : Blog List : Chintak's Blog : OUTPUT clause and triggers