再帰トリガーとRECURSIVE_TRIGGERSオプションのメモ

UPDATEトリガーで自身を定義したテーブルに対してまたUPDATEしたら無限ループに入らないの？と疑問に思ったので試してみましたと言う内容です。

説明するよりクエリを見てもらった方がわかりやすいと思うので、さっそくテーブルとUPDATEトリガーを。UPDATEトリガーでCol1の値をCol2に設定するというものです。

create table Temp(
    Id tinyint not null,
    Col1 nvarchar(5),
    Col2 nvarchar(5),
    constraint PK_Temp primary key(Id));
go

-- UPDATEトリガーでさらにUPDATE
create trigger TempTrigger
on Temp
after update
as
    print N'updated';  -- わかりやすいようにメッセージ表示
    update Temp
    set Col2 = Col1
    where Id in(
        select Id
        from inserted);
go

set nocount on;

-- テスト用データ
insert into Temp(Id, Col1, Col2)
values(1, N'abc', N'123'), (2, N'def', N'456');

select * from Temp;
/*
Id   Col1  Col2
---- ----- -----
1    abc   123
2    def   456
*/

UPDATE文を実行してみると、トリガー内のUPDATE文からはトリガーは実行されないことがわかります。

update Temp
set Col1 = N'xyz'
where Id = 1;

select * from Temp;

-- 実行結果
-- updatedのメッセージは1回だけ
/*
updated
Id   Col1  Col2
---- ----- -----
1    xyz   xyz
2    def   456
*/

おそらくこの動きがデフォルトなんでしょうが、本当にそうなのかドキュメントに載っていないか調べたところちゃんと記述がありました。

CREATE TRIGGER (Transact-SQL)

再帰トリガー

SQL Server では、ALTER DATABASE によって RECURSIVE_TRIGGERS 設定が有効になっていれば、トリガーの再帰呼び出しが行えます。

（略）

直接再帰では、アプリケーションでテーブル T1 が更新されると、この操作によってトリガー TR1 が起動し、テーブル T1 が更新されます。テーブル T1 が更新されると、トリガー TR1 が再び起動するという動作が続きます。

今回の場合は「直接再帰」と言うようですね。

こちらにも似た記述があるのでリンクを。

入れ子になったトリガーの作成

RECURSIVE_TRIGGERSオプションがOFFだと再帰呼び出しが行われず、ONだと再帰呼び出しが行われるようです。このオプションはsys.databasesカタログビューで確認できます。

sys.databases (Transact-SQL))

確認してみるとOFFになっていますね。

select is_recursive_triggers_on
from sys.databases
where name = N'Test';

/*
is_recursive_triggers_on
------------------------
0
*/

せっかくなので試しにONにしてUPDATEしてみると、再帰的に呼び出され、ネスト制限に引っかかってエラーになりました。

alter database Test
set recursive_triggers on;
go

update Temp
set Col1 = N'xyz'
where Id = 1;

-- updatedのメッセージが32個表示されてエラーになる
/*
updated
updated
-- 途中略
updated
updated
メッセージ 217、レベル 16、状態 1、プロシージャ TempTrigger、行 7
ストアド プロシージャ、関数、トリガー、またはビューの入れ子のレベルが最大値を超えました (制限 32)。
*/