SQL Serverのトリガーの基本：CREATE TRIGGERとAFTER INSERT, UPDATE, DELETEトリガーの使い方

SQL Serverのトリガーは、データベースに対する特定の操作（INSERT、UPDATE、DELETE）が実行されたときに、自動的に呼び出される特殊なストアドプロシージャです。

トリガーを利用することで、データの整合性を保つためのカスタムロジックを実行したり、操作履歴を記録することが可能です。

トリガーの概要と用途

トリガーは、データ操作に関連するイベントに応じて自動的に実行されるデータベースオブジェクトです。以下のようなシナリオで役立ちます。

  • データの履歴を記録する
  • 操作後にデータを検証する
  • 他のテーブルに影響を与えるロジックを実行する

ただし、トリガーを使いすぎるとパフォーマンスに影響する可能性があるため、適切な設計が必要です。

CREATE TRIGGER構文

SQL Serverでトリガーを作成する基本構文は次の通りです。

CREATE TRIGGER トリガー名
ON テーブル名
AFTER [INSERT, UPDATE, DELETE]
AS
BEGIN
    -- 実行したいロジック
END
  • トリガー名: 一意の名前を付けます。
  • テーブル名: トリガーを関連付ける対象のテーブル。
  • AFTER: 指定されたイベント（INSERT, UPDATE, DELETE）の後に実行。
  • BEGIN…END: 実行するロジックを記述。

AFTER INSERT, AFTER UPDATE, AFTER DELETEトリガーの実例

AFTER INSERTトリガー

新しいデータが挿入された際に呼び出されるトリガーを作成します。

シナリオ

Employeesテーブルに新しい従業員が追加された際、変更内容をAuditLogテーブルに記録します。

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Department NVARCHAR(100),
    HireDate DATE
);

CREATE TABLE AuditLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    Action NVARCHAR(50),
    EmployeeID INT,
    ChangeDate DATETIME
);

CREATE TRIGGER trg_AfterInsert_Employees
ON Employees
AFTER INSERT
AS
BEGIN
    INSERT INTO AuditLog (Action, EmployeeID, ChangeDate)
    SELECT 'INSERT', EmployeeID, GETDATE()
    FROM inserted;
END;
ポイント

insertedは、INSERT操作後に挿入されたデータを参照する特別なテーブル。

AFTER UPDATEトリガー

データが更新された際に呼び出されるトリガーです。

シナリオ

従業員の部署が更新された場合に、変更前後のデータを記録します。

CREATE TABLE DepartmentChanges (
    ChangeID INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeID INT,
    OldDepartment NVARCHAR(100),
    NewDepartment NVARCHAR(100),
    ChangeDate DATETIME
);

CREATE TRIGGER trg_AfterUpdate_Employees
ON Employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO DepartmentChanges (EmployeeID, OldDepartment, NewDepartment, ChangeDate)
    SELECT
        d.EmployeeID,
        d.Department AS OldDepartment,
        i.Department AS NewDepartment,
        GETDATE()
    FROM deleted d
    INNER JOIN inserted i
    ON d.EmployeeID = i.EmployeeID;
END;
ポイント
  • deleted: 更新前のデータを保持する特別なテーブル。
  • inserted: 更新後のデータを保持する特別なテーブル。

AFTER DELETEトリガー

データが削除された際に呼び出されるトリガーです。

シナリオ

従業員が削除された場合にその情報をログに記録します。

CREATE TRIGGER trg_AfterDelete_Employees
ON Employees
AFTER DELETE
AS
BEGIN
    INSERT INTO AuditLog (Action, EmployeeID, ChangeDate)
    SELECT 'DELETE', EmployeeID, GETDATE()
    FROM deleted;
END;
ポイント

deletedテーブルは削除された行を保持しています。

演習問題

学んだ内容を基に以下の演習問題に挑戦してみましょう。

演習問題1

Productsテーブルに対して以下の要件を満たすトリガーを作成してください。

  • 新しい商品が追加された場合、ProductLogテーブルに挿入内容を記録する。

演習問題2

Ordersテーブルで注文ステータスが更新された際に、OrderHistoryテーブルに変更履歴を記録するトリガーを作成してください。

解答例

解答例1 
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    Price DECIMAL(10, 2)
);

CREATE TABLE ProductLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    Action NVARCHAR(50),
    ProductID INT,
    ProductName NVARCHAR(100),
    Price DECIMAL(10, 2),
    ChangeDate DATETIME
);

CREATE TRIGGER trg_AfterInsert_Products
ON Products
AFTER INSERT
AS
BEGIN
    INSERT INTO ProductLog (Action, ProductID, ProductName, Price, ChangeDate)
    SELECT 'INSERT', ProductID, ProductName, Price, GETDATE()
    FROM inserted;
END;
解答例2 
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    Status NVARCHAR(50)
);

CREATE TABLE OrderHistory (
    HistoryID INT IDENTITY(1,1) PRIMARY KEY,
    OrderID INT,
    OldStatus NVARCHAR(50),
    NewStatus NVARCHAR(50),
    ChangeDate DATETIME
);

CREATE TRIGGER trg_AfterUpdate_Orders
ON Orders
AFTER UPDATE
AS
BEGIN
    INSERT INTO OrderHistory (OrderID, OldStatus, NewStatus, ChangeDate)
    SELECT
        d.OrderID,
        d.Status AS OldStatus,
        i.Status AS NewStatus,
        GETDATE()
    FROM deleted d
    INNER JOIN inserted i
    ON d.OrderID = i.OrderID;
END;

まとめ

この記事では、SQL Serverのトリガーを活用する方法を解説しました。

AFTER INSERT, UPDATE, DELETEの各トリガーを学ぶことで、データベース操作の監視やデータ変更の記録ができるようになります。

トリガーを使う際はパフォーマンスや設計を意識し、適切な場面で利用しましょう。