DMLトリガーを試す

sql-server t-sql

トリガーをほぼ使ったことがないトリガー初心者なのですが、使う機会がでてきそうで調べていました。とりあえずDMLトリガーの、さらにAFTERトリガーにしぼって気になったところを試してみたので、少しメモとして残しておきます。

まずはテスト用のテーブルとトリガーを用意しました。

-- テーブルの定義
create table Temp(
    Id tinyint not null,
    Text nvarchar(5),
    constraint PK_Temp primary key(Id));
go

-- トリガーの定義
create trigger TempTrigger
on Temp
after insert, update, delete
as
    print N'trigger';
    -- deletedテーブルで削除されたレコード、
    -- insertedテーブルで挿入されたレコードを参照できる
    select N'deleted' as Type, *
    from deleted
    union all
    select N'inserted' as Type, *
    from inserted;
    -- UPDATE関数でカラムが更新されたかどうかを判断し、メッセージを表示
    if update(Text) print N'column updated(Text)';

INSERT、UPDATE、DELETEのすべてに対してトリガーが実行されるように定義しています。また、トリガー内で使うことができる、insertedテーブル・deletedテーブルから影響を受けた行のコピーを参照できるので、それぞれSELECTしています。

UPDATE関数を使うと、指定したカラムでINSERTまたはUPDATEが行われたどうかを判断できます。

INSERT時は常にtrueが返るようです。

INSERT 動作では、列には明示的な値または暗黙的な (NULL) 値が挿入されるので、IF UPDATE は TRUE の値を返します。

DELETE時には常にfalseが返るようです。（というよりDELETEトリガーについての記述がないので、DELETE時には意味がないような気がします。）

UPDATE() は Transact-SQL の INSERT または UPDATE トリガーの内部のどこでも使用でき、そのトリガーが特定の動作を実行すべきかどうかをテストすることができます。

このトリガーを使っていくつか動きを確認してみました。

INSERTごとにトリガーが実行される

当然かもですが、INSERTを行うごとにトリガーも実行されます。次の例ではトリガーは2回実行されます。UPDATEやDELETEも同じです。insertedテーブルで挿入したレコードの内容を確認できます。

insert into Temp(Id, Text) values(1, N'a');
insert into Temp(Id, Text) values(2, N'b');

-- 実行結果
/*
trigger
Type     Id   Text
-------- ---- -----
inserted 1    a

column updated(Text)
*/
/*
trigger
Type     Id   Text
-------- ---- -----
inserted 2    b

column updated(Text)
*/

1つのINSERT文で複数のレコードを挿入する場合

次にように1つのINSERT文で複数レコードを挿入した場合は、トリガーの実行は1回です。insertedテーブルでは挿入した複数レコードを参照できます。

insert into Temp(Id, Text) values(3, N'c'), (4, N'd');

/*
trigger
Type     Id   Text
-------- ---- -----
inserted 4    d
inserted 3    c

column updated(Text)
*/

INSERT文でTextカラムの値を設定しない場合

この場合もUPDATE関数はtrueを返します。NULL値で挿入されるということですね。

insert into Temp(Id) values(5);

/*
trigger
Type     Id   Text
-------- ---- -----
inserted 5    NULL

column updated(Text)
*/

UPDATEトリガー

まずは普通にUPDATE。deletedテーブルで変更前、insertedテーブルで変更後のレコードを参照できます。UPDATE関数もtrueを返しています。

update Temp
set Text = N'e'
where Id = 5;

/*
trigger
Type     Id   Text
-------- ---- -----
deleted  5    NULL
inserted 5    e

column updated(Text)
*/

UPDATE文でTextカラムを更新しない場合

Textカラムを更新しないと、UPDATE関数はfalseを返します。

update Temp
set Id = 6
where Id = 5;

/*
trigger
Type     Id   Text
-------- ---- -----
deleted  5    e
inserted 6    e
*/

DELETEトリガー

deletedテーブルで削除したレコードを参照できます。

delete from Temp
where Id = 1 or Id = 2;

/*
trigger
Type     Id   Text
-------- ---- -----
deleted  2    b
deleted  1    a
*/

条件にマッチするデータがない場合

当然なのかもしれませんが、WHERE句の条件にマッチするデータがないときでもトリガーは実行されます。deletedテーブル、insertedテーブルは空です。この場合でもUPDATE関数はtrueを返すようです。

update Temp
set Text = N'x'
where Id = 1;  -- Idが1のレコードは存在しない

/*
trigger
Type     Id   Text
-------- ---- -----

column updated(Text)
*/

DELETE文でも同じようにトリガーが実行されます。

delete from Temp
where Id = 1;  -- Idが1のレコードは存在しない

/*
trigger
Type     Id   Text
-------- ---- -----
*/

まとまってないですし、考えていくと他にも気になるパターンは出てきてそうですが、とりあえずこの辺で・・・。