トリガーで更新されたカラムをチェックする方法

ここではマイクロソフトの 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 カラムが更新されたときにのみ、ヒストリーを記録します。このため、三回更新を実行していますが、ヒストリーテーブルには２件のレコードが記録されるはずですね。

上記のトリガーによって、どのような情報が記録されるか確認します。

SQL Server トリガー

確かに、FirstName カラムの値を変更したときはヒストリーが記録されたものの、 Email カラムの変更時にはヒストリーが記録されなかったことがわかりますね。