コメント
コメントの投稿
※名前とタイトルが入力されていないコメントでは他のコメントとの区別ができません。
入力されていないコメントには返信しませんのであらかじめご了承くださいませ。
複数トリガーの実行順序を検証してみました | SQL Server
- 2018/07/28
- 21:00
今回は SQL Server においてひとつのテーブルに複数のトリガーを設定した場合の実行順序について調査してみました。
通常のシステムではトリガーによる影響が複雑になるため、ひとつのテーブルに対してひとつの種類のトリガーを設定します。例えばテーブルA には Update トリガーがひとつと Insert トリガーがひとつを設定しますが、Insert トリガーを２つ設定することはセオリーではありません。
たまたまひとつのテーブルに複数のトリガーが設定されているシステムを耳にしたことから、そんな場合の実行順序はどのようになるのか興味を持ちましたので、検証してみることにしました。
前提条件
・Windows10 Pro 64Bit 1709
・SQL Server 2016 Developer / Standard / Enterprise 以降
１．検証用のテーブル
検証用のテーブルを用意します。１つ目のテーブルにはデータを入力する為のテーブルで備考欄にコンパイル順序をメモしておきます。２つ目のテーブルには更新日時と更新トリガー名称を保存するフィールドを用意します。データ入力用のテーブル
USE [testDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T_TriggeredTableTest1](
[Id] [int] NOT NULL,
[Field1] [int] NULL,
[Field2] [int] NULL,
[Field3] [int] NULL,
[Description] [nvarchar](50) NULL,
CONSTRAINT [PK_T_TriggeredTableTest1] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
トリガーにより追加されるデータを保存するテーブル
USE [testDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T_TriggeredTableTest2](
[Id] [int] NOT NULL,
[Field1] [int] NULL,
[Field2] [int] NULL,
[Field3] [int] NULL,
[Description] [nvarchar](50) NULL,
[UpdatedTime] [datetime] NULL,
[UpdateModule] [nvarchar](50) NULL,
CONSTRAINT [PK_T_TriggeredTableTest2] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
２．検証用のトリガー
以下のように３つのトリガーを用意し、１つ目のテーブルに入力されたデータから２つ目のテーブルにそれぞれトリガーの名称と実行時間を追加する動作をさせます。念のため既に追加されている行数をカウントして２つ目のテーブルのフィールド１に更新します。さらに、トリガー間で実行時間が同時にならない様にWAITをかけます。
トリガー１
USE [testDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_Test_1]
ON [dbo].[T_TriggeredTableTest1]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
--トリガーでINSERTした行数を取得する
DECLARE @cnt AS INT;
SET @cnt = 0;
SELECT @cnt = COUNT(*)
FROM dbo.T_TriggeredTableTest2 AS T2
;
--１秒待機する
WAITFOR DELAY '00:00:01';
--トリガー毎にデータを作成する
INSERT INTO dbo.T_TriggeredTableTest2 (
[Id]
,[Field1]
,[Field2]
,[Field3]
,[Description]
,[UpdatedTime]
,[UpdateModule]
)
SELECT Id + 1 --他のトリガーと重複しないIDに変更します。
,@cnt
,[Field2]
,[Field3]
,[Description]
,GetDate()
,'TRIGGER1' --追加したデータを識別する為にトリガーの名称を記載します。
FROM inserted
;
END
トリガー２
USE [testDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_Test_1]
ON [dbo].[T_TriggeredTableTest1]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
--トリガーでINSERTした行数を取得する
DECLARE @cnt AS INT;
SET @cnt = 0;
SELECT @cnt = COUNT(*)
FROM dbo.T_TriggeredTableTest2 AS T2
;
--１秒待機する
WAITFOR DELAY '00:00:01';
--トリガー毎にデータを作成する
INSERT INTO dbo.T_TriggeredTableTest2 (
[Id]
,[Field1]
,[Field2]
,[Field3]
,[Description]
,[UpdatedTime]
,[UpdateModule]
)
SELECT Id + 2 --他のトリガーと重複しないIDに変更します。
,@cnt
,[Field2]
,[Field3]
,[Description]
,GetDate()
,'TRIGGER2' --追加したデータを識別する為にトリガーの名称を記載します。
FROM inserted
;
END
トリガー３
USE [testDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_Test_1]
ON [dbo].[T_TriggeredTableTest1]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
--トリガーでINSERTした行数を取得する
DECLARE @cnt AS INT;
SET @cnt = 0;
SELECT @cnt = COUNT(*)
FROM dbo.T_TriggeredTableTest2 AS T2
;
--１秒待機する
WAITFOR DELAY '00:00:01';
--トリガー毎にデータを作成する
INSERT INTO dbo.T_TriggeredTableTest2 (
[Id]
,[Field1]
,[Field2]
,[Field3]
,[Description]
,[UpdatedTime]
,[UpdateModule]
)
SELECT Id + 3 --他のトリガーと重複しないIDに変更します。
,@cnt
,[Field2]
,[Field3]
,[Description]
,GetDate()
,'TRIGGER3' --追加したデータを識別する為にトリガーの名称を記載します。
FROM inserted
;
END
３．検証方法
トリガーをコンパイルする順番を変更しながら１つ目のテーブルにデータを入力していきます。（１）トリガー１から３の順番に Create でコンパイルしてデータ入力
（２）トリガー１から３の順番に Alter でコンパイルしてデータ入力
（３）トリガー３から１の順番に Create でコンパイルしてデータ入力
（４）トリガー３から１の順番に Alter でコンパイルしてデータ入力
（５）トリガー１から３の順番に Create でコンパイルした後に、トリガー３から１の順番に Alter でコンパイルしてデータ入力
４．検証結果
３つの Insert トリガーによる実行結果は以下のようになります。（１）トリガー１→３の順番で実行される
（２）トリガー１→３の順番で実行される
（３）トリガー３→１の順番で実行される
（４）トリガー３→１の順番で実行される
（５）トリガー１→３の順番で実行される
５．実行順序の結論と考察
以上のことから、複数のトリガーの実行順序は最初に Create でコンパイルした順番となるようです。その後、Alter でコンパイルしようとも実行順序は変更されません。きっと Systable の ID の順番になっているのではないでしょうか。
ただし、やはり最初にコンパイルした順序で実行順序が変化してしまうと、システムリリース時に実行順序が担保されません。１つのテーブルにはやはり１つのトリガーがセオリーだという事を改めて認識しました。
最後までお読みいただき、ありがとうございます。
他にも SQL Server に関する記事をご紹介しておりますのでご参考頂ければ幸いです。
