SQL Server の AFTER トリガーで更新履歴を残す

SQL Server の AFTER トリガーを作成する

「 SQL Server の DML トリガー 」 では、DML トリガーについて説明しました。

今回は、DML トリガーの AFTER トリガーを実際に作ってみましょう。

こちら のスクリプトを実行して、テーブルを再生成しておいてください。 全てのテーブルは使いませんが、これらのテーブルが存在している前提で進めます。


DML トリガーの AFTER トリガー

シンプルな DML トリガーの AFTER トリガーを生成する構文は次の通りです。 

CREATE TRIGGER [ トリガー名 ]
ON [ テーブル名 ]
AFTER { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
AS
BEGIN

    [ 実行したいスクリプト ]
    
END

AFTER の後に、このトリガーを有効にしたいステートメントを続けます。

データの挿入、更新、削除後に同じトリガーを実行したい場合は AFTER INSERT, UPDATE, DELETE のように 3 つ並べます。

また、AFTER トリガーは、トリガーを起こしたステートメントが正常に実行された後にのみ実行されます。

今回は、AFTER トリガーを使って、Student テーブルのデータ挿入・更新・削除の履歴を保存してみましょう。

AFTER INSERT, UPDATE, DELETE でひとつのトリガーにすることもできますが、INSERT か UPDATE か DELETE かを判断するのに余分なスクリプトが必要になります。

トリガーはデータに変更がある度に実行されるものなので、できるだけシンプルにしたいので、INSERT、UPDATE、DELETE で別々に 3 つのトリガーを作ります。


まず、次のスクリプトを実行して、StudentHistory テーブルを作っておきます。 

CREATE TABLE StudentHistory (
    StudentHistoryID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    ProcessDateTime DATETIME NULL,
    ProcessAction NVARCHAR(6) NULL,
    ProcessOrder TINYINT NULL,
    StudentID INT NULL,
    FirstName VARCHAR(50) NULL,
    LastName VARCHAR(50) NULL,
    Birthday DATE NULL,
    Gender CHAR(1) NULL
);

SQL Server の DML トリガーを作成する 1


ちょっと長いですが、Student テーブルのデータが、挿入・更新・削除された時に StudentHistory テーブルにデータを保存する AFTER トリガーを生成するスクリプトは以下の通りです。 

CREATE TRIGGER trgHistoryStudentInsert
ON Student
AFTER INSERT
AS
BEGIN
    
    DECLARE @ProcessDateTime DATETIME = GETDATE(),
            @ProcessAction   NVARCHAR(20) ='INSERT';

    INSERT INTO StudentHistory (
                ProcessDateTime,
                ProcessAction,
                ProcessOrder,
                StudentID,
                FirstName,
                LastName,
                Birthday,
                Gender
        )
        SELECT  @ProcessDateTime,
                @ProcessAction,
                NULL, --ProcessOrder,
                StudentID,
                FirstName,
                LastName,
                Birthday,
                Gender
        FROM    inserted
        ORDER BY StudentID;

END
GO
CREATE TRIGGER trgHistoryStudentUpdate
ON Student
AFTER UPDATE
AS
BEGIN
    
    DECLARE @ProcessDateTime DATETIME = GETDATE(),
            @ProcessAction   NVARCHAR(20) ='UPDATE';

    INSERT INTO StudentHistory (
                ProcessDateTime,
                ProcessAction,
                ProcessOrder,
                StudentID,
                FirstName,
                LastName,
                Birthday,
                Gender
        )
        SELECT  @ProcessDateTime,
                @ProcessAction,
                1 AS ProcessOrder,
                StudentID,
                FirstName,
                LastName,
                Birthday,
                Gender
        FROM    deleted
        UNION ALL
        SELECT  @ProcessDateTime,
                @ProcessAction,
                2 AS ProcessOrder,
                StudentID,
                FirstName,
                LastName,
                Birthday,
                Gender
        FROM    inserted
        ORDER BY StudentID,
                 ProcessOrder;

END
GO
CREATE TRIGGER trgHistoryStudentDelete
ON Student
AFTER DELETE
AS
BEGIN
    
    DECLARE @ProcessDateTime DATETIME = GETDATE(),
            @ProcessAction   NVARCHAR(20) ='DELETE';

    INSERT INTO StudentHistory (
                ProcessDateTime,
                ProcessAction,
                ProcessOrder,
                StudentID,
                FirstName,
                LastName,
                Birthday,
                Gender
        )
        SELECT  @ProcessDateTime,
                @ProcessAction,
                NULL, --ProcessOrder,
                StudentID,
                FirstName,
                LastName,
                Birthday,
                Gender
        FROM    deleted
        ORDER BY StudentID;

END

このスクリプトを実行すると、Student テーブルのトリガーの箇所に以下の３つのトリガーができるはずです。

SQL Server の DML トリガーを作成する 2


トリガーの内容的には特別なことはしておらず、StudentHistory テーブルに以下の値を挿入しています。

* ProcessDateTime: 変更日時
* ProcessAction: INSERT、 UPDATE、 DELETE のどれか
* ProcessOrder: UPDATE の場合のみ 1: 変更前 か 2: 変更後
* inserted と deleted のテーブルの値


それでは、作ったトリガーを試してみましょう。

現在の Student テーブルと StudentHistory テーブルのデータは次の通りです。 

SELECT * FROM Student;
SELECT * FROM StudentHistory;

SQL Server の DML トリガーを作成する 3


まず、二人の学生のレコードを Student テーブルに挿入します。 

INSERT INTO Student
  (FirstName, LastName, Birthday, Gender)
 VALUES
  ('Emi', 'Matsuzaki', '1981-05-12', 'F'),
  ('Hiro', 'Matsuzaki', '1981-05-12', 'M');

[ 実行後の Student テーブルと StudentHistory テーブル ] SQL Server の DML トリガーを作成する 4


先ほど挿入した二人の学生の LastName を Matsusaki に変更します。 

UPDATE  Student
SET     LastName = 'Matsusaki'
WHERE   StudentID IN (7,8);

[ 実行後の Student テーブルと StudentHistory テーブル ] SQL Server の DML トリガーを作成する 5


最後に、挿入した二人の学生のデータを削除します。 

DELETE  
FROM    Student
WHERE   StudentID IN (7,8);

[ 実行後の Student テーブルと StudentHistory テーブル ] SQL Server の DML トリガーを作成する 6

AFTER トリガーを使って、StudentHistory テーブルに変更履歴が残せましたね。


次は、INSTEAD OF トリガーを作成して、値の変更を防いでみましょう。

次へ SQL Server の INSTEAD OF トリガーで値の変更を防ぐ

© 2010-2024 SQL Server 入門