MS SQL Server - Instead Of trigger not firing After trigger

Author

Instead Of trigger not firing After trigger

JayCallas@hotmail.com

2006-03-08, 8:23 pm

According to the documentation I read, an INSTEAD OF trigger basically
rolls back the original transaction and performs whatever is coded in
it. Normally, an INSTEAD OF trigger will not cause any AFTER triggers
UNLESS the INSTEAD OF trigger executes any DML statements on the
original table.

On my SQL 2000 box, I cannot seem to get this to work. On a particular
table, I have an INSTEAD OF DELETE trigger which executes an UPDATE on
the original setting a column to 1 (soft delete logic). On this same
table, I also have a AFTER UPDATE trigger which is supposed to copy the
row to an audit table. This does not seem to work.

So I went looking on the web and came across some example code which is
supposed to demonstrate this process. THAT does not work either.
(Although when I ran the SAME code on my SQL 2005 box it worked as
expected so there does not seem to be anything wrong with the code.)

Here is the sample code I found:

drop table multiTest
go
create table multiTest (
keyVal int
)
go
insert multiTest select 1 union select 2 union select 3
go
create trigger tr_multiTest_io on multitest instead of update as
BEGIN
select 'instead of trigger firing'
delete multiTest
from multiTest inner join deleted on multiTest.keyVal =
deleted.keyVAl

insert multiTest
select *
from inserted
END -- trigger def
go
create trigger tr_multiTest_u on multitest after update as
select 'update trigger firing'
go
create trigger tr_multiTest_id on multitest after insert, delete as
select 'insert/delete trigger firing'
go

update multitest set keyVal = keyVal + 1

Any ideas on what is wrong with my server? (It is running version
8.0.2039 which I think is the lastest, if not one of the latest,
service packs.)

David Barber [MS]

2006-03-08, 8:23 pm

Is the nested triggers configuration option set to allow for trigger
nesting?

--
David Barber [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights

<JayCallas@hotmail.com> wrote in message
news:1141859250.000265.244040@z34g2000cwc.googlegroups.com...
> According to the documentation I read, an INSTEAD OF trigger basically
> rolls back the original transaction and performs whatever is coded in
> it. Normally, an INSTEAD OF trigger will not cause any AFTER triggers
> UNLESS the INSTEAD OF trigger executes any DML statements on the
> original table.
>
> On my SQL 2000 box, I cannot seem to get this to work. On a particular
> table, I have an INSTEAD OF DELETE trigger which executes an UPDATE on
> the original setting a column to 1 (soft delete logic). On this same
> table, I also have a AFTER UPDATE trigger which is supposed to copy the
> row to an audit table. This does not seem to work.
>
> So I went looking on the web and came across some example code which is
> supposed to demonstrate this process. THAT does not work either.
> (Although when I ran the SAME code on my SQL 2005 box it worked as
> expected so there does not seem to be anything wrong with the code.)
>
> Here is the sample code I found:
>
> drop table multiTest
> go
> create table multiTest (
> keyVal int
> )
> go
> insert multiTest select 1 union select 2 union select 3
> go
> create trigger tr_multiTest_io on multitest instead of update as
> BEGIN
> select 'instead of trigger firing'
> delete multiTest
> from multiTest inner join deleted on multiTest.keyVal =
> deleted.keyVAl
>
> insert multiTest
> select *
> from inserted
> END -- trigger def
> go
> create trigger tr_multiTest_u on multitest after update as
> select 'update trigger firing'
> go
> create trigger tr_multiTest_id on multitest after insert, delete as
> select 'insert/delete trigger firing'
> go
>
> update multitest set keyVal = keyVal + 1
>
>
> Any ideas on what is wrong with my server? (It is running version
> 8.0.2039 which I think is the lastest, if not one of the latest,
> service packs.)
>

JayCallas@hotmail.com

2006-03-09, 7:23 am

That was it, thank you.

So there is a difference between the server wide nested triggers
setting and the database specific recursive trigger setting. (I had
tried changing the recursive trigger setting and nothing I read
mentioned the server setting.) Is there a danger to having this set?
Anything I should be careful of so I do not get into trouble?

David Barber [MS]

2006-03-09, 8:23 pm

Nested triggers and recursive triggers are two somewhat different, though
related, constructs. Check out the Books Online topic "Using Nested
Triggers." 1 is the default setting for nested triggers, so it is not
necessarily considered risky, but it really depends on how well you
understand the triggers in your database. If possible, test your triggers
under this setting before implementing it in a production environment.
--
David Barber [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights

<JayCallas@hotmail.com> wrote in message
news:1141905272.245946.240260@v46g2000cwv.googlegroups.com...
> That was it, thank you.
>
> So there is a difference between the server wide nested triggers
> setting and the database specific recursive trigger setting. (I had
> tried changing the recursive trigger setting and nothing I read
> mentioned the server setting.) Is there a danger to having this set?
> Anything I should be careful of so I do not get into trouble?
>

Sponsored Links