トリガーの基本概念

トリガーはデータ操作イベントに自動応答する特殊なデータベースオブジェクトです。通常のストアドプロシージャとは異なり、明示的な呼び出しではなく、INSERT/UPDATE/DELETE操作の発生時に自動実行されます。主に複雑なデータ整合性制約や監査ログ管理に利用されます。

トリガータイプの分類

SQL Serverでは主に2種類のトリガーが存在します：

• DMLトリガー：データ操作言語（INSERT/UPDATE/DELETE）イベントに応答
• DDLトリガー：データ定義言語（CREATE/ALTER/DROP）イベントに応答

DMLトリガーはさらに2種類に分けられます：

システムロジックテーブルの動作

トリガー実行時には自動生成される2つの仮想テーブルが利用可能です：

実装サンプル

部門テーブルの更新監視

-- 部門テーブル変更検知トリガー
IF OBJECT_ID('trg_dept_audit', 'TR') IS NOT NULL
    DROP TRIGGER trg_dept_audit
GO
CREATE TRIGGER trg_dept_audit 
ON departments
AFTER UPDATE
AS
BEGIN
    DECLARE @oldCode CHAR(3), @newCode CHAR(3)
    
    SELECT @oldCode = dept_code FROM deleted
    SELECT @newCode = dept_code FROM inserted
    
    IF @oldCode <> @newCode
        INSERT INTO audit_log (table_name, old_value, new_value, change_time)
        VALUES ('departments', @oldCode, @newCode, GETDATE())
END
GO

従業員データの整合性チェック

-- 給与検証トリガー
IF OBJECT_ID('trg_salary_validation', 'TR') IS NOT NULL
    DROP TRIGGER trg_salary_validation
GO
CREATE TRIGGER trg_salary_validation
ON employees
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT 1 FROM inserted 
        WHERE salary < (SELECT min_salary FROM positions WHERE position_id = inserted.position_id)
        OR salary > (SELECT max_salary FROM positions WHERE position_id = inserted.position_id)
    )
    BEGIN
        RAISERROR('給与が職位の許容範囲外です', 16, 1)
        ROLLBACK TRANSACTION
    END
END
GO

ビュー更新の代替処理

-- 部署ビュー更新トリガー
IF OBJECT_ID('trg_view_department', 'TR') IS NOT NULL
    DROP TRIGGER trg_view_department
GO
CREATE TRIGGER trg_view_department
ON department_view
INSTEAD OF UPDATE
AS
BEGIN
    UPDATE dept_main 
    SET dept_name = i.dept_name,
        location = i.location
    FROM dept_main d
    INNER JOIN inserted i ON d.dept_id = i.dept_id
    
    UPDATE dept_extension
    SET manager_id = i.manager_id
    FROM dept_extension e
    INNER JOIN inserted i ON e.dept_id = i.dept_id
END
GO

監査ログ自動記録

-- 汎用監査トリガー
CREATE TABLE audit_log (
    log_id INT IDENTITY PRIMARY KEY,
    table_name VARCHAR(50),
    operation VARCHAR(10),
    user_name VARCHAR(50),
    change_time DATETIME DEFAULT GETDATE()
)

CREATE TRIGGER trg_audit_all
ON employees
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
        INSERT INTO audit_log (table_name, operation, user_name)
        VALUES ('employees', 'UPDATE', SYSTEM_USER)
    
    ELSE IF EXISTS (SELECT * FROM inserted)
        INSERT INTO audit_log (table_name, operation, user_name)
        VALUES ('employees', 'INSERT', SYSTEM_USER)
    
    ELSE
        INSERT INTO audit_log (table_name, operation, user_name)
        VALUES ('employees', 'DELETE', SYSTEM_USER)
END
GO

管理コマンド

-- トリガーの無効化
DISABLE TRIGGER trg_salary_validation ON employees

-- トリガーの有効化
ENABLE TRIGGER trg_salary_validation ON employees

-- トリガー定義の確認
EXEC sp_helptext 'trg_dept_audit'

-- 作成済みトリガーの一覧
SELECT name, type_desc FROM sys.triggers