Recursive Triggers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Recursive Triggers

Author

Topic

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-01-07 : 04:49:49

quote:


use pubs
sp_configure 'nested triggers',1
go
reconfigure
go
alter database pubs
set RECURSIVE_TRIGGERS ON
go
create table abcd (recid int)
go
create trigger abcd_trigger 
	on abcd 
	instead of insert 
as 
begin
	declare @recid int
	select @recid = recid + 1 from inserted
	insert into abcd values (@recid)
end
go
insert into abcd values (1)
go
select * from abcd
go
drop trigger abcd_trigger
drop table abcd
go

Why does this insert value as 2 even though I have enabled recursive triggers.... Gurus .. any answers????

And as for changing the database options ... please check what they are before executing this post so that you can reset them later.

Attitude is everything

{The Enigma}

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-01-07 : 05:31:04

Extract from BOL ...

If an INSTEAD OF trigger defined on a table executes a statement against the table that would usually fire the INSTEAD OF trigger again, the trigger is not called recursively. Instead, the statement is processed as if the table had no INSTEAD OF trigger and starts the chain of constraint operations and AFTER trigger executions. For example, if a trigger is defined as an INSTEAD OF INSERT trigger for a table, and the trigger executes an INSERT statement on the same table, the INSERT statement executed by the INSTEAD OF trigger does not call the trigger again. The INSERT executed by the trigger starts the process of performing constraint actions and firing any AFTER INSERT triggers defined for the table.

Raymond

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-01-07 : 05:44:02

Thanks .. that helps a lot .

Attitude is everything

{The Enigma}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources