InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 09:50:41 GMT20http://www.sqlservercentral.com/Forums/Topic1387252-23-1.aspxThank you for the link!Thu, 14 Mar 2013 19:38:23 GMTEKDhttp://www.sqlservercentral.com/Forums/Topic1387252-23-1.aspxThis thread seems to have gotten to the bottom of the behavior you are seeing:[u][url=http://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspx]http://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspx[/url][/u]Thu, 14 Mar 2013 14:17:05 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1387252-23-1.aspxHi everyone,I have a problem that "instead of delete" trigger is not fired from "after insert" trigger. Reading BOL it seems that "after" triggers should fire "instead of" triggers. I have nested triggers option set to 1, recursive triggers option is on. Below is an example. The example is very simplified (no constraints, no real life logic) and I use it just to demonstrate the problem. Table t1 has 2 triggers - instead of delete trigger justwrites to t1History table. Second trigger calls delete from t1 if the same row as valid inserted already exists with status 0.You can see that when test statement1 is called, history row is not created even though the old row is deleted.Can you please help me understand why the "instead of delete" trigger is not fired from "after" trigger? I have SQL Server 2008 R2.select SERVERPROPERTY('ProductVersion'), SERVERPROPERTY('ProductLevel'), SERVERPROPERTY('Edition')10.50.2500.0 SP1 Enterprise Edition (64-bit)Thank you,EKDcreate table t1History( itemCode int, itemName varchar(200), eventdate smalldatetime)create table t1( itemCode int, itemName varchar(200), itemStatus int --0- old, 1- new)insert into t1 (itemCode, itemName, itemStatus) values (1, '111', 0)insert into t1 (itemCode, itemName, itemStatus) values (1, '222', 1)gocreate trigger t1AfterInsert on t1for insertasdelete From t1 where exists (Select 1 from inserted where inserted.itemName = t1.itemName and inserted.itemStatus = 1 and t1.itemStatus = 0)gocreate trigger t1InsteadOfDelete on t1instead of deleteasinsert into t1History select itemCode, itemName, GETDATE() from deleteddelete from t1 from t1join deleted d on d.itemCode = t1.itemCode and d.itemName = t1.itemName and d.itemStatus = t1.itemStatusgo--test statement1---- the below statement calls after insert trigger and teh row with status 0 is successfully deleted, but no row in t1History is created!insert into t1 (itemCode, itemName, itemStatus) values (1, '111', 1)--test statement2--the below statement correctly fires instead of delete triggerdelete From t1 where itemName = '222'Wed, 21 Nov 2012 00:05:23 GMTEKD