再帰トリガーと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 */
おそらくこの動きがデフォルトなんでしょうが、本当にそうなのかドキュメントに載っていないか調べたところちゃんと記述がありました。
再帰トリガー
SQL Server では、ALTER DATABASE によって RECURSIVE_TRIGGERS 設定が有効になっていれば、トリガーの再帰呼び出しが行えます。
(略)
直接再帰では、アプリケーションでテーブル T1 が更新されると、 この操作によってトリガー TR1 が起動し、テーブル T1 が更新されます。 テーブル T1 が更新されると、トリガー TR1 が再び起動するという動作が続きます。
今回の場合は「直接再帰」と言うようですね。
こちらにも似た記述があるのでリンクを。
RECURSIVE_TRIGGERSオプションがOFFだと再帰呼び出しが行われず、ONだと再帰呼び出しが行われるようです。このオプションはsys.databasesカタログビューで確認できます。
確認してみると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)。 */