SQLServerでのInsert,Update,Deleteトリガーの使い方

SQLServerにはテーブルに対して「トリガー」を設定することができ、テーブルに挿入、更新、削除されるごとにそれらの処理を呼び出すことが可能です。

INSERT、UPADTE、DELETEトリガーを扱ってみる

SQLServerにはテーブルに対して「トリガー」を設定することができ、テーブルに挿入、更新、削除されるごとにそれらの処理を呼び出すことが可能です。

テーブルの準備

今回はデータを挿入、更新、削除されるテーブルとそのトリガーでレコードが自動的に挿入されるテーブルを用意します。

CREATE TABLE test_main_table
(
     no      INT
    ,name    VARCHAR(20)
)
GO
CREATE TABLE test_sub_table
(
     no      INT
    ,name    VARCHAR(20)
    ,trigger_kind INT
    ,trigger_date DATETIME
)
GO

INSERTトリガー

test_main_tableにデータが挿入されると呼び出されるトリガーを作成します。

--INSERT後に呼ばれるトリガー
CREATE TRIGGER test_main_insert ON test_main_table 
    FOR  INSERT
AS
    INSERT INTO test_sub_table
        ( no, name, trigger_kind, trigger_date )
             SELECT no, name, 1, GETDATE() from inserted
GO

このトリガーではtest_main_tableに挿入された内容と、トリガーの種別「１」をtest_sub_tableに設定します。

test_main_tableに挿入された行のデータは「inserted」という特殊なテーブルから取得できます。

UPDATEトリガー

test_main_tableのデータが更新されると呼び出されるトリガーを作成します。

--UPDATE後に呼ばれるトリガー
CREATE TRIGGER test_main_update ON test_main_table 
    FOR  UPDATE
AS
    INSERT INTO test_sub_table
        ( no, name, trigger_kind, trigger_date )
             SELECT no, name, 2, GETDATE() from inserted
GO

このトリガーではtest_main_tableに更新された内容と、トリガーの種別「２」をtest_sub_tableに設定します。

test_main_tableで更新された行のデータは「inserted」という特殊なテーブルから取得できます。

DELETEトリガー

test_main_tableのデータが削除されると呼び出されるトリガーを作成します。

--DELETE後に呼ばれるトリガー
CREATE TRIGGER test_main_DELETE ON test_main_table 
    FOR  DELETE
AS
    INSERT INTO test_sub_table
        ( no, name, trigger_kind, trigger_date )
             SELECT no, name, 3, GETDATE() from deleted
GO

このトリガーではtest_main_tableで削除された内容と、トリガーの種別「３」をtest_sub_tableに設定します。

test_main_tableで削除された行のデータは「deleted」という特殊なテーブルから取得できます。

INSERT,UPDATE,DELETEトリガーの動作確認

test_main_tableにデータを挿入し、そのデータを更新後、削除します。

INSERT INTO test_main_table VALUES( 1, '太郎')
UPDATE test_main_table SET name = '次郎' WHERE no = 1
DELETE FROM test_main_table WHERE no = 1

上記のSQLを実行後test_sub_tableの内容を検索します。

SELECT no, name, trigger_kind, trigger_date FROM test_sub_table ORDER BY trigger_date

実行結果は

no   name   trigger_kind   trigger_date
1    太郎       1         2014-01-14 11:24:50.127
1    次郎       2         2014-01-14 11:24:50.137
1    次郎       3         2014-01-14 11:24:50.143

となり、データ挿入時、更新時、削除時にそれぞれのトリガーが動作していることが確認できます。
（trigger_kindがそれぞれのトリガーに対応しています。）

トリガーの注意事項

トリガーで実行したSQLでエラーが発生するとトランザクションの状況によってはロールバックが発生し、メインテーブルの変更も無効になったりするので注意が必要です。