トリガーで更新されたカラムをチェックする方法
ここではマイクロソフトの SQL Server でトリガー内で更新されたカラムをチェックする方法を記載します。
ひとことでいえば、COLUMN 句を利用します。
INSERT または UPDATE のトリガーで COLUMN(カラム名) とすることで、そのカラムに変更があった場合、COLUMN は TRUE を返し、 変更がない場合は FALSE を返します。
従って、IF COLUMN(カラム名) ... とすることで、そのカラムが更新されたかどうかチェックすることが可能です。
では早速、テスト用のデータベースを作成し、動作確認しましょう。
テスト用のテーブルは次の通りです。Users テーブルと、その変更を記録する UsersHistory というテーブルを用意します。
CREATE TABLE Users(
[UserID] [int] NOT NULL,
[FirstName] [nvarchar](200) NOT NULL,
[LastName] [nvarchar](200) NOT NULL,
[Email] [varchar](200) NOT NULL
);
CREATE TABLE UsersHistory (
[HistorySeq] INT IDENTITY(1,1) NOT NULL,
[RecordType] CHAR(1)
CHECK([RecordType] IN ('I','U','D')) NOT NULL,
[UserID] [int] NOT NULL,
[FirstName] [nvarchar](200) NOT NULL,
[LastName] [nvarchar](200) NOT NULL,
[Email] [varchar](200) NOT NULL
);
Users テーブルに以下のトリガーを作成します。
ここではコードの意図をはっきりさせるため、 INSERT、UPDATE、DELETE それぞれに対するトリガーを別々に記載します。
CREATE TRIGGER RecordHistoryInsertUsers
ON Users
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO UsersHistory (
RecordType, UserID, FirstName, LastName, Email
)
SELECT 'I', UserID, FirstName,
LastName, Email
FROM inserted;
END
GO
CREATE TRIGGER RecordHistoryUpdateUsers
ON Users
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE(FirstName) OR UPDATE(LastName)
BEGIN
INSERT INTO UsersHistory (
RecordType, UserID, FirstName,
LastName, Email
)
SELECT 'U', UserID, FirstName,
LastName, Email
FROM inserted;
END
END
GO
CREATE TRIGGER RecordHistoryDeleteUsers
ON Users
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO UsersHistory (
RecordType, UserID, FirstName,
LastName, Email
)
SELECT 'D', UserID, FirstName, LastName, Email
FROM deleted;
END
GO
INSERT のトリガーでは挿入されたレコードをそのままヒストリーテーブルに記録。 UPDATE では、FirstName カラムまたは LastName カラムが更新された場合にのみ、更新後のデータを記録。 DELETE では削除されたレコードを記録します。
トリガーの動作確認
テストデータとして、次のスクリプトを実行します。
INSERT INTO Users VALUES (
1000, 'Taro', 'Yamada', 'taroy@foo.com');
UPDATE Users
SET FirstName = 'X'
WHERE UserID = 1000;
UPDATE Users
SET Email = 'xyz'
WHERE UserID = 1000;
UPDATE Users
SET FirstName = 'Y'
WHERE UserID = 1000;
DELETE Users
WHERE UserID = 1000;
はじめに一件、レコードをインサートし、次いで FirstName データの更新、Email の更新、再度 FirstName の更新を実行。 最後にレコードをデリートします。
前述の通り、UPDATE のトリガーでは FirstName または LastName カラムが更新されたときにのみ、 ヒストリーを記録します。このため、三回更新を実行していますが、ヒストリーテーブルには2件のレコードが記録されるはずですね。
上記のトリガーによって、どのような情報が記録されるか確認します。
確かに、FirstName カラムの値を変更したときはヒストリーが記録されたものの、 Email カラムの変更時にはヒストリーが記録されなかったことがわかりますね。