DML トリガーの使い方

SQL Server のトリガー構築と最適化

Florin Angelescu

Instructor

DML トリガーを使う理由

データ操作時に処理を開始する
データ操作を防止する
データやオブジェクトの変更を追跡する
監査とセキュリティを強化する

AFTER と INSTEAD OF の選択

CREATE TRIGGER MyFirstAfterTrigger
ON Table1
-- 発火イベント（UPDATE）後に実行
AFTER UPDATE
AS
{trigger_actions_section};

CREATE TRIGGER MyFirstInsteadOfTrigger
ON Table2
-- 発火イベント（UPDATE）の代わりに実行
INSTEAD OF UPDATE
AS
{trigger_actions_section};

AFTER と INSTEAD OF の選択

AFTER トリガー

初期イベントでトリガーが発火

初期イベントが実行される

続いてトリガー処理が実行

AFTER トリガーの動作

INSTEAD OF トリガー

初期イベントでトリガーが発火

初期イベントは実行されない

代わりにトリガー処理が実行

INSTEAD OF トリガーの動作

AFTER トリガーの使用例

売上テーブルにデータを挿入
クレンジング手順を開始
結果でレポートを生成
DBA に通知

CREATE TRIGGER SalesNewInfoTrigger
ON Sales
AFTER INSERT
AS

EXEC sp_cleansing @Table = 'Sales';

EXEC sp_generateSalesReport;

EXEC sp_sendnotification;

INSTEAD OF トリガーの使用例

| Brand | Model    | Power | Stock |
|-------|----------|-------|-------|
| Ecco  | Standard | 30W   | 30    |
| Miry  | Buma     | 45W   | 0     |
| Lume  | Ultra    | 50W   | 0     |

一部モデルのPowerが変更される

CREATE TRIGGER BulbsStockTrigger
ON Bulbs
INSTEAD OF INSERT
AS

INSTEAD OF トリガーの使用例

| Brand | Model    | Power | Stock |
|-------|----------|-------|-------|
| Ecco  | Standard | 30W   | 30    |
| Miry  | Buma     | 50W   | 100   |
| Lume  | Ultra    | 52W   | 100   |

一部モデルのPowerが変更される
在庫0の商品のみ更新する

CREATE TRIGGER BulbsStockTrigger
ON Bulbs
INSTEAD OF INSERT
AS

IF EXISTS (SELECT * FROM Bulbs AS b
INNER JOIN inserted AS i
           ON b.Brand = i.Brand
          AND b.Model = i.Model
WHERE b.Stock = 0)
BEGIN
    UPDATE b
    SET b.Power = i.Power,
        b.Stock = i.Stock
    FROM Bulbs AS b
    INNER JOIN inserted AS i
                ON b.Brand = i.Brand
                  AND b.Model = i.Model
    WHERE b.Stock = 0
END

INSTEAD OF トリガーの使用例

| Brand | Model    | Power | Stock |
|-------|----------|-------|-------|
| Ecco  | Standard | 30W   | 30    |
| Miry  | Buma     | 50W   | 100   |
| Lume  | Ultra    | 52W   | 100   |
| Ecco  | Standard | 35W   | 100   |

一部モデルのPowerが変更される
在庫0の商品のみ更新する
在庫がある商品は新規行を追加する

-- 先頭部分はスペースの都合で省略
IF EXISTS (SELECT * FROM Bulbs AS b
INNER JOIN inserted AS i
           ON b.Brand = i.Brand
          AND b.Model = i.Model
WHERE b.Stock = 0)
BEGIN
    UPDATE b
    SET b.Power = i.Power,
        b.Stock = i.Stock
    FROM Bulbs AS b
    INNER JOIN inserted AS i
                ON b.Brand = i.Brand
                  AND b.Model = i.Model
    WHERE b.Stock = 0
END
ELSE
    INSERT INTO Bulbs
    SELECT * FROM inserted;

練習問題

SQL Server のトリガー構築と最適化