SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


"instead of delete" trigger is not fired from "after" trigger


"instead of delete" trigger is not fired from "after" trigger

Author
Message
EKD
EKD
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 126
Hi 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 just
writes 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,
EKD

create 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)
go
create trigger t1AfterInsert on t1
for insert
as
delete From t1
where exists (Select 1 from inserted where inserted.itemName = t1.itemName and inserted.itemStatus = 1 and t1.itemStatus = 0)
go
create trigger t1InsteadOfDelete on t1
instead of delete
as
insert into t1History
select itemCode, itemName, GETDATE() from deleted

delete from t1
from t1
join deleted d on d.itemCode = t1.itemCode and d.itemName = t1.itemName and d.itemStatus = t1.itemStatus
go

--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 trigger
delete From t1 where itemName = '222'
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15179 Visits: 14396
This thread seems to have gotten to the bottom of the behavior you are seeing:

http://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspx

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
EKD
EKD
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 126
Thank you for the link!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search