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

SQL Server の INSTEAD OF トリガー

「 AFTER トリガーで更新履歴を残す」では、履歴を残すための AFTER トリガーを作成してみました。

今回は、DML トリガーの INSTEAD OF　トリガーを作ってみましょう。

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

DML トリガーの INSTEAD OF トリガー

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

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

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

INSTEAD OF トリガーは、トリガーを起こしたステートメントの動作の代わりに実行されます。　つまり、トリガーを起こしたステートメントは実行されません。

ですので、例えば INSTEAD OF トリガーで値のエラーチェックを行いたい場合、[ 実行したいスクリプト ] には、エラーがなかった時に実行されて欲しい、トリガーを起こした元のスクリプトと同様の動きをするスクリプトも含める必要があります。

INSTEAD OF トリガーを使うと、エラーを出さずに、一部のカラムだけを更新するというようなことも可能です。

では、実際に INSTEAD OF トリガーを使ってみましょう。

次のスクリプトを実行して、Student テーブルに StudentNumber カラムを追加してください。

StudentNumber カラムはプライマリーキーではありませんが、NOT NULL でユニークな値で、学生の識別に使われ、一度生成されたら二度と変更されない、というビジネスルールがあるとします。

ALTER TABLE Student ADD StudentNumber VARCHAR(8) NULL;
GO

UPDATE  Student
SET     StudentNumber = LEFT(FirstName, 1) 
                        + LEFT(LastName, 1) 
                        + RIGHT('000000' + CAST(StudentID AS NVARCHAR), 6);
GO

ALTER TABLE Student ALTER COLUMN StudentNumber VARCHAR(8) NOT NULL;
ALTER TABLE Student ADD UNIQUE (StudentNumber);
GO

SELECT * FROM Student;

そして、以下のスクリプトを実行して、StudentNumber を変更しようとした場合にエラーを起こして更新させない、INSTEAD OF トリガーを作成します。

CREATE TRIGGER trgStudentNumberUpdatePrevent
ON Student
INSTEAD OF UPDATE
AS
BEGIN
    
    IF EXISTS
        (SELECT *
         FROM   inserted AS I
                    INNER JOIN deleted AS D
                        ON I.StudentID = D.StudentID
         WHERE  I.StudentNumber <> D.StudentNumber)
    BEGIN

        RAISERROR (N'StudentNumber は変更できません。', 16, 10); 
         
    END
    ELSE
    BEGIN

        UPDATE  S
        SET     FirstName = I.FirstName,
                LastName = I.LastName,
                Birthday = I.Birthday,
                Gender = I.Gender
        FROM    Student AS S
                    INNER JOIN inserted AS I
                        ON S.StudentID = I.StudentID;

    END

END

更新時にトリガーを走らせたいので、INSTEAD OF UPDATEで、UPDATE ステートメントを対象に INSTEAD OF トリガーを生成しています。

プライマリーキーの StudentID で deleted（更新前）と inserted（更新後）テーブルを結合して、StudentNumber が前後で違う場合は RAISERROR でエラーを起こしています。

RAISERROR の第二引数は severity （重大度レベル）で、16 はユーザーが訂正できる一般的なエラーです。

StudentNumber に変更がない場合は、inserted　テーブルの値を使って、Student テーブルの StudentNumber 以外のカラムを更新しています。

それでは、次の UPDATE ステートメントを実行して、INSTEAD OF トリガーを試してみましょう。

UPDATE  Student
SET     Gender = 'F',
        StudentNumber = 'MM000002'
WHERE   StudentID = 1;

StudentNumber を変更しようとしたので、「 StudentNumber は変更できません。」というエラーになり、Student テーブルのデータは更新されませんでした。

それでは、StudentNumber 以外のデータを更新してみます。

UPDATE  Student
SET     Gender = 'F'
WHERE   StudentID = 1;

エラーが出ずに、Gender が更新されていますね。