【SQL Server】DMLトリガーの作成方法

まー
2023-03-20  2023-06-18
【SQL Server】DMLトリガーの作成方法

 SQL ServerのDMLトリガー作成方法を記載します。

DMLトリガーとは

DMLトリガー（Data Modification Language trigger）は、データベースのテーブルやビューに対してINSERT、UPDATE、DELETEなどのデータ操作（DML）が行われると実行されるプログラムです。
DMLトリガーは、データの整合性チェック、テーブルの更新、データの変更履歴管理などに使用されます。

SQLServerのDMLトリガー

SQLServerで使用するDMLトリガーと行情報を保持する仮想テーブル、DMLトリガーの作成構文について記載します。

トリガーの種類

SQLServerのDMLトリガー「INSTEAD OFトリガー」と「AFTERトリガー」があり、実行タイミングが異なります。

INSTEAD OFトリガー

DML操作前に、トリガーのSQLステートメントを実行します。
このとき、元のDML操作は実行されずにトリガーで実行したSQLステートメントが代替結果として返されます。

AFTERトリガー

DML操作後にトリガーのSQLステートメントを実行します。


仮想テーブル

トリガーを実行したときの行情報は「DELETEDテーブル」と「INSERTEDテーブル」の仮想テーブルに保持しています。仮想テーブルに保持される行情報はDML操作により異なります。

INSERTEDテーブル

DML操作後の行情報を保持します。（追加した行、更新後の行情報保持）

DELETEDテーブル

DML操作前の行情報を保持します。（削除した行、更新前の行情報保持）

INSERT UPDATE DELETE
INSERTEDテーブル 追加する行情報 更新後の行情報 -
DELETEDテーブル - 更新前の行情報 削除する行情報


DMLトリガーの作成構文

DMLトリガーの作成構文は以下になります。

CREATE [ OR ALTER ] TRIGGER [ トリガー名 ]
ON { テーブル名 | ビュー名 }
{ AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS
BEGIN
    -- トリガーの処理内容を記述
END

CREATE OR ALTERとすると、DMLトリガー作成および更新を行います。

SQLServerのDMLトリガー実装例

SQLServerのDMLトリガーを使用した実装例を記載します。

<環境>
Microsoft SQL Server 2022 Developer Edition (64-bit)

<テーブル>
今回は以下のテーブルを使用します。
・Productテーブル　　　　 商品テーブル
・ProductHistoryテーブル　商品履歴テーブル

SQLServerDMLトリガー実装ER図
/****** テーブル作成 *****/
CREATE TABLE Product (
    product_id   INT IDENTITY(1,1) PRIMARY KEY,
    product_name VARCHAR(50),
    price        DECIMAL(10,0)
)

CREATE TABLE ProductHistory (
    history_id   INT IDENTITY(1,1) PRIMARY KEY,
    product_id    INT  REFERENCES Product(product_id),
    product_name VARCHAR(50),
    price        DECIMAL(10,0),
    action       VARCHAR(50),
    action_date  DATETIME
)

データの登録前にチェックする

商品テーブルに価格が0以下の商品が登録されないようにチェックします。
データ登録前にチェックするため、INSTEAD OFトリガーを使用しています。
価格が0以下ならメッセージを返します。それ以外のときはdeletedテーブルの状態を判定してINSERTまたはUPDATEを実行します。 

CREATE OR ALTER TRIGGER [dbo].[PriceCheck]
ON  [dbo].[Product]
INSTEAD OF INSERT, UPDATE
AS
BEGIN
    IF EXISTS (SELECT * FROM inserted WHERE price <= 0)
        BEGIN
            RAISERROR('価格が0以下です。', 16, 1);
        END
    ELSE IF EXISTS (SELECT * FROM deleted)
        BEGIN
            /* 更新前のレコードがあるため更新する。（deletedテーブルにレコードあり）*/
            UPDATE t SET t.product_name = i.product_name, t.price = i.price
            FROM Product AS t 
            INNER JOIN inserted AS i ON t.product_id = i.product_id;
	    END
	ELSE
        BEGIN
            /* 更新前のレコードがないため追加する。（deletedテーブルにレコードなし）*/
            INSERT INTO Product ( product_name, price) 
            SELECT product_name, price 
            FROM inserted;
        END
END

データの変更履歴を管理する

商品テーブルを登録したあとに、AFTERトリガーを使用して商品履歴テーブルにデータを追加します。
商品履歴テーブルはdeletedテーブルの状態から、商品テーブルでINSERTとUPDATEのどちらが実行されたか判定して書き込みます。

CREATE OR ALTER TRIGGER [dbo].[ProductHistoryUpdate]
ON [dbo].[Product]
AFTER INSERT, UPDATE
AS
BEGIN
IF EXISTS (SELECT * FROM deleted)
    BEGIN
        /* 更新前のレコードがあるため'UPDATE'を登録する。（deletedテーブルにレコードあり）*/
        INSERT INTO ProductHistory (product_id, product_name, price, action, action_date)
        SELECT i.product_id, i.product_name, i.price, 'UPDATE', SYSDATETIME()
        FROM inserted i;
    END
ELSE
    BEGIN
        /* 更新前のレコードがないため'INSERT'を登録する。（deletedテーブルにレコードなし）*/
        INSERT INTO ProductHistory (product_id, product_name, price, action, action_date)
        SELECT i.product_id, i.product_name, i.price, 'INSERT', SYSDATETIME()
        FROM inserted i;
    END
END


Next Post Previous Post