[2k5] 간접 재귀 트리거(Recursive Triggers)

1. /**********************************************************************************************
   -- Title : [2k5] 간접 재귀 트리거(Recursive Triggers)
   -- Reference : hanbitbook.co.kr
   -- Key word : nested triggers is_recursive_triggers_on create 트리거 sp_configure
   **********************************************************************************************/
   use master;
   go
2. drop database triggerdb;
   go
3. create database triggerdb;
   go
4. use triggerdb;
   go
5. -- 간접 재귀 트리거가 되기 위해선 아래 두 옵션이 on 되어야 한다.
   exec sp_configure 'nested triggers'
   select name, is_recursive_triggers_on from sys.databases where name = 'triggerdb'
   go
6. alter database triggerdb
    set recursive_triggers on;
   go
7. exec sp_dboption 'triggerdb','recursive triggers'; --on
   go
8. create table recua (id int identity, txt nvarchar(10)); -- 간접 재귀트리거용 테이블a
   create table recub (id int identity, txt nvarchar(10)); -- 간접 재귀트리거용 테이블b
   go
9. create trigger trg_recua
   on recua
   after insert
   as
    declare @id int
    select @id = trigger_nestlevel() -- 현재 트리거 레벨값
    print '트리거레벨==> ' + cast(@id as char(5))
    insert into recub values ('간접재귀트리거');
   go
10. create trigger trg_recub
    on recub
    after insert
    as
     declare @id int
     select @id = trigger_nestlevel() -- 현재 트리거 레벨값
     print '트리거레벨==> ' + cast(@id as char(5))
     insert into recua values ('간접재귀트리거');
    go
11. select * from sys.triggers;
    go
12. insert into recub values ('처음입력값');
    go
13. select * from recua;
    select * from recub;
    go
14. alter trigger trg_recua
    on recua
    after  insert
    as
     if ( (select trigger_nestlevel() ) >= 32 )
      return
     declare @id int
     select @id = trigger_nestlevel() -- 현재트리거레벨값
     print '트리거레벨==> ' + cast(@id as char(5))
     insert into recub values ('간접재귀트리거');
    go
15. alter trigger trg_recub
    on recub
    after  insert
    as
     if ( (select trigger_nestlevel() ) >= 32 )
      return
     declare @id int
     select @id = trigger_nestlevel() -- 현재트리거레벨값
     print '트리거레벨==> ' + cast(@id as char(5))
     insert into recua values ('간접재귀트리거');
    go
16. insert into recua values ('처음입력값');
    go
17. select * from recua;
    select * from recub;
    go
18. select * from sys.triggers;