fc2ブログ

記事一覧

複数トリガーの実行順序を検証してみました | SQL Server


今回は SQL Server においてひとつのテーブルに複数のトリガーを設定した場合の実行順序について調査してみました。
通常のシステムではトリガーによる影響が複雑になるため、ひとつのテーブルに対してひとつの種類のトリガーを設定します。例えばテーブルA には Update トリガーがひとつと Insert トリガーがひとつを設定しますが、Insert トリガーを2つ設定することはセオリーではありません。
たまたまひとつのテーブルに複数のトリガーが設定されているシステムを耳にしたことから、そんな場合の実行順序はどのようになるのか興味を持ちましたので、検証してみることにしました。


前提条件
・Windows10 Pro 64Bit 1709
・SQL Server 2016 Developer / Standard / Enterprise 以降



1.検証用のテーブル

検証用のテーブルを用意します。1つ目のテーブルにはデータを入力する為のテーブルで備考欄にコンパイル順序をメモしておきます。2つ目のテーブルには更新日時と更新トリガー名称を保存するフィールドを用意します。

データ入力用のテーブル
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



2.検証用のトリガー

以下のように3つのトリガーを用意し、1つ目のテーブルに入力されたデータから2つ目のテーブルにそれぞれトリガーの名称と実行時間を追加する動作をさせます。
念のため既に追加されている行数をカウントして2つ目のテーブルのフィールド1に更新します。さらに、トリガー間で実行時間が同時にならない様にWAITをかけます。


トリガー1
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
    ;

    --1秒待機する
    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


トリガー2
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
    ;

    --1秒待機する
    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


トリガー3
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
    ;

    --1秒待機する
    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



3.検証方法

トリガーをコンパイルする順番を変更しながら1つ目のテーブルにデータを入力していきます。

(1)トリガー1から3の順番に Create でコンパイルしてデータ入力
(2)トリガー1から3の順番に Alter でコンパイルしてデータ入力
(3)トリガー3から1の順番に Create でコンパイルしてデータ入力
(4)トリガー3から1の順番に Alter でコンパイルしてデータ入力
(5)トリガー1から3の順番に Create でコンパイルした後に、トリガー3から1の順番に Alter でコンパイルしてデータ入力

sqlserver_trigger_order_01.png




4.検証結果

3つの Insert トリガーによる実行結果は以下のようになります。

(1)トリガー1→3の順番で実行される
(2)トリガー1→3の順番で実行される
(3)トリガー3→1の順番で実行される
(4)トリガー3→1の順番で実行される
(5)トリガー1→3の順番で実行される

sqlserver_trigger_order_02.png




5.実行順序の結論と考察

以上のことから、複数のトリガーの実行順序は最初に Create でコンパイルした順番となるようです。その後、Alter でコンパイルしようとも実行順序は変更されません。
きっと Systable の ID の順番になっているのではないでしょうか。
ただし、やはり最初にコンパイルした順序で実行順序が変化してしまうと、システムリリース時に実行順序が担保されません。1つのテーブルにはやはり1つのトリガーがセオリーだという事を改めて認識しました。





最後までお読みいただき、ありがとうございます。
他にも SQL Server に関する記事をご紹介しておりますのでご参考頂ければ幸いです。



関連記事

コメント

コメントの投稿

※名前とタイトルが入力されていないコメントでは他のコメントとの区別ができません。

 入力されていないコメントには返信しませんのであらかじめご了承くださいませ。

※ニックネームでも良いので必ずご入力ください。

    

※必ずご入力ください。

    
    

※必ずご入力ください。

※技術的な質問には環境やエラーについて正確かつ詳細にお教えください。

・正確なエラーの内容

・Windowsのバージョン番号

・Visual Studioのバージョン

・機器の型番

・アプリやソフトのバージョン

    

カテゴリ別記事一覧

広告

プロフィール

石河 純


著者名 :石河 純
自己紹介:素人上がりのIT技術者。趣味は卓球・車・ボウリング

IT関連の知識はざっくりとこんな感じです。
【OS関連】
WindowsServer: 2012/2008R2/2003/2000/NT4
Windows: 10/8/7/XP/2000/me/NT4/98
Linux: CentOS RedHatLinux9
Mac: macOS Catalina 10.15 / Mojave 10.14 / High Sierra 10.13 / Sierra 10.12 / OSX Lion 10.7.5 / OSX Snow Leopard 10.6.8
【言語】
VB.net ASP.NET C#.net Java VBA
Xamarin.Forms
【データベース】
Oracle 10g/9i
SQLServer 2016/2008R2/2005/2000
SQLAnywhere 16/11/8
【BI/レポートツール】
Cognos ReportNet (IBM)
Microsoft PowerBI
ActiveReport (GrapeCity)
CrystalReport
【OCX関連】
GrapeCity InputMan SPREAD MultiRow GridView
【ネットワーク関連】
CCNP シスコ技術者認定
Cisco Catalyst シリーズ
Yamaha RTXシリーズ
FireWall関連
【WEB関連】
SEO SEM CSS jQuery IIS6/7 apache2

休みの日は卓球をやっています。
現在、卓球用品通販ショップは休業中です。