Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Unanswered: recursive triggers

    Code:
    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.
    Last edited by Enigma; 01-07-04 at 05:40.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  2. #2
    Join Date
    Nov 2003
    Posts
    94
    It's the INSTEAD OF clause. This causes the statements in the trigger to replace the action of the triggering sql.

    Use FOR INSERT,UPDATE and the trigger will recurse as expected.

  3. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •