トリガを作成します。トリガは、指定したテーブルで特定のデータ修正ステートメントの実行を試みると自動的に実行される特殊なストアド プロシージャです。Microsoft® SQL Server™ では、INSERT、UPDATE、または DELETE の各ステートメントごとに複数のトリガを作成できます。
構文
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
[ WITH APPEND ]
[NOT FOR REPLICATION]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
}
引数
trigger_name
トリガの名前です。トリガ名は、識別子の規則に従い、データベース内で一意である必要があります。トリガの所有者名の指定は省略可能です。
Table | view
トリガが実行されるテーブルまたはビューです。トリガ テーブルまたはトリガ ビューとも呼ばれます。テーブルまたはビューの所有者名の指定は省略可能です。
WITH ENCRYPTION
CREATE TRIGGER のテキストを含んでいる syscomments エントリを暗号化します。WITH ENCRYPTION を使用すると、そのトリガを SQL Server レプリケーションの一部としてパブリッシュできなくなります。
AFTER
トリガを起動する SQL ステートメントに指定されているすべての操作の実行が成功したときにのみ、トリガが起動されることを指定します。このトリガが実行される前に、連鎖参照操作および制約チェックの実行もすべて成功している必要があります。
指定されているキーワードが FOR だけである場合は、AFTER が既定値になります。
AFTER トリガは、ビューでは定義できません。
INSTEAD OF
トリガを起動する SQL ステートメントの代わりにそのトリガが実行されることを指定します。したがって、トリガを起動するステートメントの動作は無視されます。
テーブルまたはビューでは、INSERT、UPDATE、または DELETE の各ステートメントごとに 1 つだけ INSTEAD OF トリガを定義できます。ただし、各ビューに INSTEAD OF トリガがあるビューで、ビューを定義することができます。
INSTEAD OF トリガは、WITH CHECK OPTION オプションが指定された更新可能なビューでは使用できません。WITH CHECK OPTION が指定された更新可能なビューに INSTEAD OF トリガが追加されると、エラーが発生します。ユーザーは、INSTEAD OF トリガを定義する前に、ALTER VIEW を使用して WITH CHECK OPTION を削除する必要があります。
{ [DELETE] [,] [INSERT] [,] [UPDATE] }
このテーブルまたはビューに対してデータ修正ステートメントを試みた場合、どのステートメントがトリガをアクティブにするかを指定するキーワードです。少なくとも 1 つのオプションを指定する必要があります。トリガ定義をするこのキーワードは、指定する順序、組み合わせを問いません。複数のオプションを指定するときは、オプションをカンマで区切ります。
INSTEAD OF トリガの場合、目的のテーブルに ON DELETE 連鎖参照操作を指定している参照関係があるときは、DELETE オプションを指定できません。同様に、目的のテーブルに ON UPDATE 連鎖参照操作を指定している参照関係があるときは、UPDATE オプションを指定できません。
WITH APPEND
既存の種類の追加トリガを追加する必要があることを示します。この省略可能な句を使用する必要があるのは、互換性レベルが 65 以下の場合だけです。互換性レベルが 70 以上の場合、既存の種類の追加のトリガを追加するために WITH APPEND 句を使用する必要はありません。これは、互換性レベルの設定が 70 以上である場合の CREATE TRIGGER の既定の動作です。詳細については、「sp_dbcmptlevel」を参照してください。
WITH APPEND は、INSTEAD OF トリガと共に使用したり、AFTER トリガが明示的に指定されている場合は使用できません。旧バージョンとの互換性上の理由から、WITH APPEND を使用できるのは、FOR が指定されている場合 (INSTEAD OF と AFTER のどちらも指定されていない) だけです。WITH APPEND と FOR (AFTER トリガと解釈される) は、今後のリリースではサポートされません。
NOT FOR REPLICATION
トリガに関係するテーブルをレプリケーション プロセスが修正するときに、トリガを実行してはいけないことを示します。
AS
トリガが行う動作です。
sql_statement
トリガ条件とトリガ動作です。トリガ条件は、DELETE、INSERT、または UPDATE ステートメントを試みた場合に、どのようなトリガ動作を行うかを決める追加条件を指定します。
Transact-SQL ステートメントで指定されたトリガ動作は、ユーザーが INSERT、UPDATE、または DELETE の操作を試みると有効になります。
トリガには任意の数と種類の Transact-SQL ステートメントを含めることができます。トリガは、データ変更ステートメントに基づいてデータの検査や変更を行うためのものであり、ユーザーには値を返しません。トリガ内の Transact-SQL ステートメントには、流れ制御言語がよく使用されます。CREATE TRIGGER ステートメントでは、特別なテーブルがいくつか使用されます。
n
複数の Transact-SQL ステートメントをトリガに含めることができることを示すプレースホルダです。IF UPDATE (column) ステートメントには、UPDATE (column) 句を繰り返すことによって、複数の列を含めることができます。
IF UPDATE (column)
指定された列で INSERT 動作または UPDATE 動作を検査します。DELETE 動作では使われません。1 つ以上の列を指定できます。ON 句でテーブル名が指定されているので、IF UPDATE 句では、列名の前にテーブル名を挿入しないでください。複数の列で INSERT 動作または UPDATE 動作を検査するには、最初の UPDATE(column) 句に続けて、別の UPDATE(column) 句を指定します。INSERT 動作では、列には明示的な値または暗黙の (NULL) 値が挿入されるので、IF UPDATE は TRUE の値を返します。
注 IF UPDATE (column) 句は、IF ステートメント、IF...ELSE ステートメントまたは WHILE ステートメントとまったく同じ役割を果たし、BEGIN...END ブロックを使用できます。詳細については、「流れ制御言語」を参照してください。
UPDATE(column) は、トリガの内部のどこでも使用できます。
column
INSERT 動作または UPDATE 動作を検査する列の名前です。この列のデータ型は、SQL Server がサポートしているものであれば、どのよのようなデータ型でもかまいません。ただし、計算列を使用することはできません。詳細については、「データ型」を参照してください。
IF (COLUMNS_UPDATED())
記述されている 1 つ以上の列が挿入されるか更新されるかについて、INSERT トリガまたは UPDATE トリガでのみチェックを行います。COLUMNS_UPDATED は、テーブル内で挿入または削除された列を示す varbinary ビット パターンを返します。
COLUMNS_UPDATED 関数は左から右の順にビットを返し、最下位ビットが左端にきます。左端のビットがテーブル内の最初の列を表し、左から 2 番目のビットは 2 番目の列を、それ以下のビットも同様の順で列を表します。トリガが作成されるテーブルに列が 9 個以上ある場合、COLUMNS_UPDATED は、複数のバイトを返します。最下位バイトが左端になります。
INSERT 動作では、列には明示的な値または暗黙の (NULL) 値が挿入されるので、COLUMNS_UPDATED は、すべての列に対して TRUE の値を返します。COLUMNS_UPDATED は、トリガの内部のどこでも使用できます。
bitwise_operator
比較で使用するビットごとの演算子です。
updated_bitmask
実際に更新または挿入される列の整数ビットマスクです。たとえば、テーブル t1 が列 C1、C2、C3、C4、および C5 で構成されているものとします。テーブル t1 に UPDATE トリガを実行する場合、列 C2、C3、C4 がすべて更新されたかどうかを調べるには、値 14 を指定します。列 C2 だけが更新されたかどうかを調べるには、値 2 を指定します。
comparison_operator
比較演算子です。updated_bitmask に指定したすべての列が実際に更新されていることを確認するには、等号 (=) を使用します。updated_bitmask で指定された列のいずれかが更新されたかどうかを調べるには、不等号 (>) を使用します。
column_bitmask
列が実際に更新または挿入されたかどうかを調べるための整数ビットマスクです。
解説
トリガはビジネス ルールおよびデータの整合性を設定するためによく使用されます。SQL Server では、テーブル作成ステートメント (ALTER TABLE と CREATE TABLE) を通じて、宣言可能な参照整合性 (DRI) が提供されています。ただし、データベース間の参照整合性は DRI では提供されません。参照整合性 (テーブルの主キーと外部キーとの間の関係に関する規則) を設定するには、主キー制約と外部キー制約 (ALTER TABLE と CREATE TABLE の PRIMARY KEY キーワードと FOREIGN KEY キーワード) を使用します。トリガ テーブルに制約が存在する場合、これらは INSTEAD OF トリガの実行の後で、AFTER トリガの実行の前に検査されます。制約違反があると、INSTEAD OF トリガ動作はロールバックされ、AFTER トリガは実行 (起動) されません。
テーブルで実行される最初と最後の AFTER トリガを、sp_settriggerorder を使用して指定できます。1 つのテーブルで、INSERT、UPDATE、DELETE の各操作に対して指定できる最初の AFTER トリガと最後の AFTER トリガは、それぞれ 1 つだけです。同じテーブルにほかの AFTER トリガが指定されている場合、トリガはランダムに実行されます。
ALTER TRIGGER ステートメントを使って最初と最後のトリガを変更した場合、変更したトリガに設定されていた最初と最後を示す属性が削除されます。sp_settriggerorder を使用して順序の属性値を再設定する必要があります。
AFTER トリガは、トリガを起動する SQL ステートメント (更新または削除されるオブジェクトに関連付けられているすべての連鎖参照操作および制約チェックを含む) の実行が成功した後でのみ実行されます。AFTER トリガは、トリガを起動するステートメントおよびそのステートメントによって発生するすべての連鎖参照 UPDATE および DELETE 操作による影響を確認します。
トリガの制限
CREATE TRIGGER はバッチ内の最初のステートメントにする必要があり、1 つのテーブルに対してのみ適用されます。
トリガは現在のデータベース内でしか作成できませんが、トリガはほかのデータベース内のオブジェクトを参照することができます。
トリガを修飾するトリガ所有者名を指定するときは、テーブル名を同じ方法で修飾します。
同じ CREATE TRIGGER ステートメント内で、1 つのトリガ動作を複数のユーザー操作 (たとえば、INSERT と UPDATE) に対して定義することができます。
INSTEAD OF DELETE/UPDATE トリガは、外部キーを持ち、DELETE/UPDATE 操作に対する連鎖操作が定義されているテーブルでは定義できません。
トリガの内部では任意の SET ステートメントを指定できます。選択した SET オプションは、トリガ実行中有効で、終了後は元の設定に戻ります。
トリガが起動すると、ストアド プロシージャの場合と同様に、結果が呼び出し側アプリケーションに返されます。トリガを実行したことによって結果がアプリケーションに返されないようにするには、結果を返す SELECT ステートメントまたは変数の代入を実行するステートメントをトリガに含めないようにします。ユーザーに結果を返す SELECT ステートメントまたは変数の代入を実行するステートメントを含むトリガは、返される結果をトリガ テーブルの修正が許されているすべてのアプリケーションに書き込む必要があるという点で、特殊な扱いを必要とします。トリガ内で変数の代入を実行する必要があるときは、トリガの先頭で SET NOCOUNT ステートメントを使用して、結果セットが返されないようにします。
TRUNCATE TABLE ステートメントは、DELETE トリガの対象になりません。TRUNCATE ステートメントが有効であると、これは WHERE 句なしの DELETE と同様の動作 (すべての行を削除) となりますが、ログに記録されないため、トリガを実行することはできません。TRUNCATE TABLE ステートメントの権限は、特に指定のない限りテーブル所有者であり、この権限は譲渡できないため、DELETE トリガを TRUNCATE TABLE ステートメントで囲むという不注意な使用法については、そのテーブルの所有者以外は心配する必要はありません。
WRITETEXT ステートメントは、ログされているかどうかにかかわらず、トリガをアクティブにしません。
次の Transact-SQL ステートメントはトリガでは使用できません。
| ALTER DATABASE | CREATE DATABASE | DISK INIT |
| DISK RESIZE | DROP DATABASE | LOAD DATABASE |
| LOAD LOG | RECONFIGURE | RESTORE DATABASE |
| RESTORE LOG | | |
注 SQL Server はシステム テーブルに対するユーザー定義のトリガをサポートしていないため、システム テーブルではユーザー定義のトリガを作成しないことをお勧めします。
複数のトリガ
SQL Server では、各データ修正イベント (DELETE、INSERT または UPDATE) に対して複数のトリガを作成することができます。たとえば、既に UPDATE トリガが作成されているテーブルに対して CREATE TRIGGER FOR UPDATE を実行すると、追加の更新トリガが作成されます。以前のバージョンでは、各テーブルで、各データ修正イベントに対して 1 つのトリガしか許されませんでした。
注 互換性レベルが 70 のときの CREATE TRIGGER の既定の動作は、トリガ名が異なれば既存のトリガの追加トリガを追加するという動作です。トリガ名が同じであれば、SQL Server はエラー メッセージを返します。ただし、互換性レベルが 65 以下であれば、トリガ名が異なる場合でも、CREATE TRIGGER ステートメントで作成されたすべての新しいトリガが同じ種類の既存のトリガを置き換えます。詳細については、「sp_dbcmptlevel」を参照してください。
再帰トリガ
sp_dboption で recursive triggers の設定が使用可能になっていれば、SQL Server ではトリガの再帰呼び出しが可能です。
再帰トリガでは 2 種類の再帰が可能です。
間接再帰では、アプリケーションがテーブル T1 を更新し、それがトリガ TR1 を起動し、テーブル T2 を更新します。この方式では、次にトリガ TR2 が起動し、テーブル T1 を更新します。
直接再帰では、アプリケーションがテーブル T1 を更新し、それがトリガ TR1 を起動し、テーブル T1 を更新します。テーブル T1 が更新されたので、トリガ TR1 が再び起動する、という動作が続きます。
次に、直接トリガ再帰と間接トリガ再帰の両方を使用する例を示します。テーブル T1 に対して 2 つの更新トリガ TR1、TR2 が定義されていると仮定します。トリガ TR1 はテーブル T1 を再帰的に更新します。UPDATE ステートメントは、TR1 と TR2 を 1 回ずつ実行します。さらに、TR1 の実行は、TR1 (再帰的) と TR2 の実行を起動します。あるトリガの inserted テーブルと deleted テーブルには、そのトリガを呼び出した UPDATE ステートメントのみに対応する行が含まれます。
注 上記の動作は、sp_dboption の recursive triggers の設定が使用可能になっている場合にのみ実行されます。あるイベントに対して定義されている複数のトリガが実行される順序に決まりはありません。個々のトリガは自己完結している必要があります。
recursive triggers の設定を無効にすると、直接再帰のみが無効になります。間接再帰も無効にするには、sp_configure を使用して nested triggers サーバー オプションを 0 に設定します。
トリガのいずれかが ROLLBACK TRANSACTION を実行すると、ネストのレベルにかかわらず、それ以降のトリガは実行されません。
ネストしたトリガ
トリガは 32 段階のレベルまでネストすることができます。トリガが、別のトリガを持つテーブルを変更した場合、この 2 番目のトリガがアクティブになり、次に 3 番目のトリガを呼び出すという具合に、順にアクティブになります。このトリガのチェーンで無限ループが発生するとネスト レベルを超過し、トリガはキャンセルされます。ネストしたトリガを使用不可にするには、sp_configure の nested triggers オプションを 0 (オフ) に設定します。既定の設定では、ネスト トリガは許可されています。ネストしたトリガがオフになっていると、sp_dboption の recursive triggers の設定に関係なく、再帰トリガも使用不可になります。
名前の遅延解決
SQL Server では、Transact-SQL のストアド プロシージャ、トリガ、バッチが、コンパイル時に存在しないテーブルを参照することができます。この機能は名前の遅延解決と呼ばれています。ただし、Transact-SQL のストアド プロシージャ、トリガまたはバッチが、ストアド プロシージャまたはトリガで定義されているテーブルを参照すると、sp_dbcmptlevel の実行によって設定される互換性レベルが 65 に設定されているときに限り、作成時に警告が発生します。バッチが使用される場合は、コンパイル時に警告が発生します。参照されるテーブルが存在しない場合は、実行時にエラー メッセージが返されます。詳細については、「」を参照してください。
権限
CREATE TRIGGER 権限は、特に指定のない限り、トリガが定義されているテーブルの所有者、固定サーバー ロール sysadmin、および固定データベース ロール db_owner および db_ddladmin のメンバに与えられ、譲渡できません。
ユーザーがテーブルまたはビューからデータを取得するには、そのテーブルまたはビューに対する SELECT ステートメント権限を持っている必要があります。ユーザーがテーブルまたはビューの内容を更新するには、そのテーブルまたはビューに対する INSERT、DELETE、および UPDATE のステートメント権限を持っている必要があります。
ビューに INSTEAD OF トリガが存在する場合、ユーザーがそのビューに対して INSERT、DELETE、および UPDATE ステートメントを実行するには、ステートメントの実行によってその操作がビューに対して実際に実行されるかどうかにかかわらず、INSERT、DELETE、および UPDATE 特権を持っている必要があります。
例
A. トリガを事前通知と組み合わせて使用する
titles テーブルに、だれかがデータを追加したり、データを変更したりしようとすると、クライアントに対してメッセージを表示するトリガです。
注 メッセージ 50009 は、sysmessages の中にあるユーザー定義のメッセージです。ユーザー定義メッセージの作成の詳細については、「sp_addmessage」を参照してください。
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'reminder' AND type = 'TR')
DROP TRIGGER reminder
GO
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE
AS RAISERROR (50009, 16, 10)
GO
B. トリガを電子メール メッセージと組み合わせて使用する
titles テーブルが更新されると、特定のユーザー (MaryM) に電子メールを送るトリガです。
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'reminder' AND type = 'TR')
DROP TRIGGER reminder
GO
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE, DELETE
AS
EXEC master..xp_sendmail 'MaryM',
'Don''t forget to print a report for the distributors.'
GO
C. 従業員と jobs テーブルとの間でトリガ ビジネス ルールを使用する
CHECK 制約は、列レベルまたはテーブル レベルの制約が定義されている列しか参照できないので、テーブル間にまたがる制約 (ここでは、ビジネス ルール) はすべて、トリガとして定義する必要があります。
この例では、給与の基礎となる従業員職務レベルが追加または更新されたときに、指定された従業員職務レベル (job_lvls) がその職務に対して定義された範囲内にあるかどうかをチェックします。各該当の範囲を得るには、jobs テーブルを参照する必要があります。
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'employee_insupd' AND type = 'TR')
DROP TRIGGER employee_insupd
GO
CREATE TRIGGER employee_insupd
ON employee
FOR INSERT, UPDATE
AS
/* Get the range of level for this job type from the jobs table. */
DECLARE @min_lvl tinyint,
@max_lvl tinyint,
@emp_lvl tinyint,
@job_id smallint
SELECT @min_lvl = min_lvl,
@max_lvl = max_lvl,
@emp_lvl = i.job_lvl,
@job_id = i.job_id
FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id
JOIN jobs j ON j.job_id = i.job_id
IF (@job_id = 1) and (@emp_lvl <> 10)
BEGIN
RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
ROLLBACK TRANSACTION
END
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
BEGIN
RAISERROR ('The level for job_id:%d should be between %d and %d.',
16, 1, @job_id, @min_lvl, @max_lvl)
ROLLBACK TRANSACTION
END
D. 名前の遅延解決を使用する
この例では、名前の遅延解決の使用法を示す 2 つのトリガを作成します。
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'trig1' AND type = 'TR')
DROP TRIGGER trig1
GO
-- Creating a trigger on a nonexistent table.
CREATE TRIGGER trig1
on authors
FOR INSERT, UPDATE, DELETE
AS
SELECT a.au_lname, a.au_fname, x.info
FROM authors a INNER JOIN does_not_exist x
ON a.au_id = x.au_id
GO
-- Here is the statement to actually see the text of the trigger.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
WHERE o.type = 'TR' and o.name = 'trig1'
-- Creating a trigger on an existing table, but with a nonexistent
-- column.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'trig2' AND type = 'TR')
DROP TRIGGER trig2
GO
CREATE TRIGGER trig2
ON authors
FOR INSERT, UPDATE
AS
DECLARE @fax varchar(12)
SELECT @fax = phone
FROM authors
GO
-- Here is the statement to actually see the text of the trigger.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
WHERE o.type = 'TR' and o.name = 'trig2'
E. COLUMNS_UPDATED を使用する
次の例では 2 つのテーブルを作成します。1 つは employeeData テーブルで、もう 1 つは auditEmployeeData テーブルです。人事部のメンバは、機密扱いの従業員給与支払い名簿情報が格納された employeeData テーブルを修正できます。従業員の社会保障番号 (SSN)、年間給与、または銀行口座番号に変更があると、監査レコードが生成され、auditEmployeeData 監査テーブルに挿入されます。
COLUMNS_UPDATED() 関数を使用して、機密扱いの従業員情報が格納されている列に対する変更をすばやく検査できます。この方法で COLUMNS_UPDATED() が正しく動作するのは、テーブルの最初の 8 列に対する変更を検出する場合だけです。
USE pubs
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'employeeData')
DROP TABLE employeeData
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'auditEmployeeData')
DROP TABLE auditEmployeeData
GO
CREATE TABLE employeeData (
emp_id int NOT NULL,
emp_bankAccountNumber char (10) NOT NULL,
emp_salary int NOT NULL,
emp_SSN char (11) NOT NULL,
emp_lname nchar (32) NOT NULL,
emp_fname nchar (32) NOT NULL,
emp_manager int NOT NULL
)
GO
CREATE TABLE auditEmployeeData (
audit_log_id uniqueidentifier DEFAULT NEWID(),
audit_log_type char (3) NOT NULL,
audit_emp_id int NOT NULL,
audit_emp_bankAccountNumber char (10) NULL,
audit_emp_salary int NULL,
audit_emp_SSN char (11) NULL,
audit_user sysname DEFAULT SUSER_SNAME(),
audit_changed datetime DEFAULT GETDATE()
)
GO
CREATE TRIGGER updEmployeeData
ON employeeData
FOR update AS
/*Check whether columns 2, 3 or 4 has been updated. If any or all of columns 2, 3 or 4 have been changed, create an audit record. The bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14. To check if all columns 2, 3, and 4 are updated, use = 14 in place of >0 (below).*/
IF (COLUMNS_UPDATED() & 14) > 0
/*Use IF (COLUMNS_UPDATED() & 14) = 14 to see if all of columns 2, 3, and 4 are updated.*/
BEGIN
-- Audit OLD record.
INSERT INTO auditEmployeeData
(audit_log_type,
audit_emp_id,
audit_emp_bankAccountNumber,
audit_emp_salary,
audit_emp_SSN)
SELECT 'OLD',
del.emp_id,
del.emp_bankAccountNumber,
del.emp_salary,
del.emp_SSN
FROM deleted del
-- Audit NEW record.
INSERT INTO auditEmployeeData
(audit_log_type,
audit_emp_id,
audit_emp_bankAccountNumber,
audit_emp_salary,
audit_emp_SSN)
SELECT 'NEW',
ins.emp_id,
ins.emp_bankAccountNumber,
ins.emp_salary,
ins.emp_SSN
FROM inserted ins
END
GO
/*Inserting a new employee does not cause the UPDATE trigger to fire.*/
INSERT INTO employeeData
VALUES ( 101, 'USA-987-01', 23000, 'R-M53550M', N'Mendel', N'Roland', 32)
GO
/*Updating the employee record for employee number 101 to change the salary to 51000 causes the UPDATE trigger to fire and an audit trail to be produced.*/
UPDATE employeeData
SET emp_salary = 51000
WHERE emp_id = 101
GO
SELECT * FROM auditEmployeeData
GO
/*Updating the employee record for employee number 101 to change both the bank account number and social security number (SSN) causes the UPDATE trigger to fire and an audit trail to be produced.*/
UPDATE employeeData
SET emp_bankAccountNumber = '133146A0', emp_SSN = 'R-M53550M'
WHERE emp_id = 101
GO
SELECT * FROM auditEmployeeData
GO
F. COLUMNS_UPDATED を使用して 9 列以上を検査する
テーブルの最初の 8 列以外の列に影響する更新を検査する場合は、SUBSTRING を使用して COLUMNS_UPDATED から返される適切なビットを検査する必要があります。この例では、Northwind.dbo.Customers テーブルの 3、5、または 9 番目の列に影響する更新を検査します。
USE Northwind
DROP TRIGGER tr1
GO
CREATE TRIGGER tr1 ON Customers
FOR UPDATE AS
IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(3-1))
+ power(2,(5-1)))
AND (SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1)))
)
PRINT 'Columns 3, 5 and 9 updated'
GO
UPDATE Customers
SET ContactName=ContactName,
Address=Address,
Country=Country
GO
関連項目
ALTER TABLE
ALTER TRIGGER
CREATE TABLE
DROP TRIGGER
sp_depends
sp_help
sp_helptext
sp_rename
sp_settriggerorder
sp_spaceused