CREATE TRIGGER (Transact-SQL)

DML トリガ、DDL トリガ、またはログオン トリガを作成します。トリガとは、特殊な種類のストアド プロシージャであり、データベース サーバーでイベントが発生したときに自動的に実行されます。DML トリガは、ユーザーがデータ操作言語 (DML) イベントを介してデータを変更しようとしたときに実行されます。DML イベントは、テーブルやビューに対する INSERT、UPDATE、または DELETE ステートメントによって発生するイベントです。

注意
これらのトリガは、テーブル行が影響を受けるかどうかにかかわらず、有効なイベントが発生したときに起動されます。

DDL トリガは、さまざまなデータ定義言語 (DDL) イベントに対応して実行されます。本来、これらのイベントは、Transact-SQL の CREATE、ALTER、DROP ステートメント、および DDL に類似した処理を実行するシステム ストアド プロシージャに対応するものです。ログオン トリガは、ユーザー セッションの確立時に発生する LOGON イベントに応答して起動されます。トリガは、Transact-SQL ステートメントから直接作成できます。また、Microsoft.NET Framework 共通言語ランタイム (CLR) で作成され、SQL Server インスタンスにアップロードされたアセンブリのメソッドを使用して、トリガを作成することもできます。SQL Server では、特定のステートメントに対して複数のトリガを作成できます。

セキュリティに関する注意
上位の特権の下では、トリガ内の悪意のあるコードを実行できます。この脅威を緩和する方法の詳細については、「トリガのセキュリティの管理」を参照してください。

Transact-SQL 構文規則

構文

Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)
CREATE TRIGGER [ schema_name . ]trigger_name 
ON { table | view } 
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF } 
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
[ WITH APPEND ] 
[ NOT FOR REPLICATION ] 
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

<dml_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name

Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE TRIGGER trigger_name 
ON { ALL SERVER | DATABASE } 
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<ddl_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name

Trigger on a LOGON event (Logon Trigger)
CREATE TRIGGER trigger_name 
ON ALL SERVER 
[ WITH <logon_trigger_option> [ ,...n ] ]
{ FOR| AFTER } LOGON  
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<logon_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name

引数

• schema_name
  DML トリガが属しているスキーマの名前を指定します。DML トリガのスコープは、そのトリガが作成されたテーブルまたはビューのスキーマです。DDL トリガまたはログオン トリガに対しては schema_name を指定できません。

• trigger_name
  トリガの名前を指定します。trigger_name は、識別子の規則に従う必要があります。ただし、trigger_name の先頭に # または ## を指定することはできません。

• table |view
  DML トリガを実行するテーブルまたはビューを指定します。これらはトリガ テーブルまたはトリガ ビューとも呼ばれます。テーブルまたはビューの完全修飾名の指定は省略可能です。ビューは INSTEAD OF トリガでのみ参照できます。DML トリガは、ローカルまたはグローバルの一時テーブルに対しては定義できません。

• DATABASE
  DDL トリガのスコープを現在のデータベースに適用します。これを指定すると、現在のデータベースで event_type または event_group が発生するたびにトリガが起動します。

• ALL SERVER
  DDL トリガまたはログオン トリガのスコープを現在のサーバーに適用します。これを指定すると、現在のサーバーの任意の場所で event_type または event_group が発生するたびにトリガが起動します。

• WITH ENCRYPTION
  CREATE TRIGGER ステートメントのテキストを暗号化します。WITH ENCRYPTION を使用すると、トリガを SQL Server レプリケーションの一部としてパブリッシュできなくなります。WITH ENCRYPTION は、CLR トリガに対しては指定できません。

• EXECUTE AS
  トリガを実行するセキュリティ コンテキストを指定します。これにより、トリガが参照するデータベース オブジェクトの権限を検証するときに、SQL Server インスタンスが使用するユーザー アカウントを制御できます。

  詳細については、「EXECUTE AS 句 (Transact-SQL)」を参照してください。

• FOR | AFTER
  AFTER を指定した場合は、トリガ元の SQL ステートメントで指定したすべての処理が正常に実行された場合のみ、DML トリガが起動されます。このトリガの実行前には、連鎖して行われるすべての参照操作と制約チェックも完了している必要があります。

  指定したキーワードが FOR だけの場合は、AFTER が既定値になります。

  AFTER トリガは、ビューに対しては定義できません。

• INSTEAD OF
  トリガを起動する SQL ステートメントの代わりに (INSTEAD OF)、DML トリガを実行します。したがって、トリガを起動するステートメントの操作は無効になります。DDL トリガまたはログオン トリガでは INSTEAD OF を指定できません。

  テーブルまたはビューでは、INSERT、UPDATE、または DELETE の各ステートメントに定義できる INSTEAD OF トリガは 1 つだけですが、INSTEAD OF トリガが指定されているビューごとに、ビューを定義することができます。

  INSTEAD OF トリガは、WITH CHECK OPTION を使用する更新可能なビューでは使用できません。WITH CHECK OPTION が指定されている更新可能なビューに INSTEAD OF トリガを追加した場合、SQL Server ではエラーが発生します。ユーザーは、INSTEAD OF トリガを定義する前に、ALTER VIEW を使用して WITH CHECK OPTION を削除する必要があります。

• { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
  テーブルまたはビューに対して DML トリガが試行されたとき、そのトリガを有効にするデータ変更ステートメントを指定します。少なくとも 1 つのオプションを指定する必要があります。これらのオプションは、トリガ定義において、どのような順序や組み合わせでも指定できます。

  INSTEAD OF トリガの場合、目的のテーブルに連鎖的な ON DELETE 参照操作を指定している参照関係があるときは、DELETE オプションを指定できません。同様に、目的のテーブルに連鎖的な ON UPDATE 参照操作を指定している参照関係があるときは、UPDATE オプションを指定できません。

• event_type
  実行後に DDL トリガを起動する Transact-SQL 言語イベントの名前を指定します。DDL トリガで使用できるイベントの一覧については、「DDL イベント」を参照してください。

• event_group
  Transact-SQL 言語イベントの定義済みグループの名前を指定します。DDL トリガは、event_group に属する Transact-SQL 言語イベントの実行後に起動します。DDL トリガで使用できるイベント グループの一覧については、「DDL イベント グループ」を参照してください。

  event_group は、対応するイベントの種類を sys.trigger_events カタログ ビューに追加した場合、CREATE TRIGGER が終了した後でマクロとしても動作します。

• WITH APPEND
  既存のトリガに対して、新しいトリガを追加します。WITH APPEND は、INSTEAD OF トリガと共に使用したり、AFTER トリガが明示的に指定されている場合は使用できません。旧バージョンとの互換性上の理由から、WITH APPEND を使用できるのは、FOR が指定されている場合 (INSTEAD OF と AFTER のどちらも指定されていない場合) だけです。WITH APPEND は、EXTERNAL NAME が指定されている場合、つまり、トリガが CLR トリガの場合は指定できません。

  重要
  WITH APPEND は、次期バージョンの MicrosoftSQL Server では削除される予定です。新しい開発作業では、WITH APPEND の使用は避け、現在このオプションを使用しているアプリケーションは修正するようにしてください。

• NOT FOR REPLICATION
  トリガに関係するテーブルがレプリケーション エージェントによって変更されるときに、トリガを実行しないことを示します。詳細については、「NOT FOR REPLICATION を使用した制約、ID、およびトリガの制御」を参照してください。

• sql_statement
  トリガの条件と動作を指定します。トリガの条件では、試行した DML イベント、DDL イベント、またはログオン イベントによってトリガ動作が実行されるかどうかを判定するための補足の条件を指定します。

  Transact-SQL ステートメントで指定されたトリガ動作は、操作が試行されると有効になります。

  トリガには、いくつかの例外を除き、任意の数と種類の Transact-SQL ステートメントを含めることができます。詳細については、「解説」を参照してください。トリガは、データ変更またはデータ定義ステートメントに基づいてデータをチェックまたは変更するものであり、ユーザーに値は返されません。トリガ内の Transact-SQL ステートメントには、フロー制御言語が主に使用されます。

  DML トリガでは、deleted および inserted 論理 (概念) テーブルが使用されます。論理テーブルは、トリガが定義されるテーブル、つまり、ユーザー操作の対象となるテーブルと構造的に類似しています。deleted および inserted テーブルには、ユーザー操作によって変更される行の古い値または新しい値が格納されます。たとえば、deleted テーブルのすべての値を取得するには、次のように指定します。

  SELECT *
  FROM deleted
  

  詳細については、「inserted テーブルと deleted テーブルの使用」を参照してください。

  DDL トリガおよびログオン トリガでは、EVENTDATA (Transact-SQL) 関数を使用して、トリガ起動イベントに関する情報を取得できます。詳細については、「EVENTDATA 関数の使用」を参照してください。

  SQL Server では、テーブルやビューの INSTEAD OF トリガによって、text、ntext、または image 型の列を更新できます。

  重要
  ntext、text、および image の各データ型は、将来のバージョンの MicrosoftSQL Server で削除される予定です。新しい開発作業では、これらのデータ型の使用は避け、現在これらのデータ型を使用しているアプリケーションは修正するようにしてください。代わりに、nvarchar(max)、varchar(max)、varbinary(max) を使用してください。AFTER トリガおよび INSTEAD OF トリガでは、inserted および deleted テーブルの varchar(MAX)、nvarchar(MAX)、varbinary(MAX) 型のデータがサポートされます。

• < method_specifier >
  CLR トリガに対して、トリガにバインドするアセンブリのメソッドを指定します。このメソッドは引数を受け取らず、void を返す必要があります。class_name は、有効な SQL Server 識別子で、アセンブリの表示設定が指定されたアセンブリ内にクラスとして存在する必要があります。クラス名に名前空間とその区切り文字のピリオド (.) が含まれる場合は、クラス名を角かっこ ([ ]) または二重引用符 (" ") で囲む必要があります。入れ子にされたクラスは使用できません。

  注意
  既定では、CLR コードを実行する SQL Server の機能はオフになっています。マネージ コード モジュールを参照するデータベース オブジェクトを作成、変更、削除することはできますが、sp_configure によって clr enabled Option が有効化されていない場合、これらの参照は SQL Server インスタンスでは実行されません。

説明

DML トリガ

DML トリガは主に、ビジネス ルールとデータの整合性を設定するために使用します。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 ステートメントが正常に実行された後にのみ実行されます。このステートメントの実行には、更新または削除されるオブジェクトに関連付けられている連鎖的なすべての参照操作と制約チェックの実行も含まれます。

テーブルに定義された INSTEAD OF トリガによって、そのテーブルに対して、通常は INSTEAD OF トリガを再起動するステートメントが実行された場合、トリガの再帰呼び出しは行われません。代わりに、そのステートメントでは、テーブルに INSTEAD OF トリガが存在しないものとして処理が行われ、制約操作と AFTER トリガの実行が連鎖的に開始されます。たとえば、あるテーブルに INSTEAD OF INSERT トリガが定義されており、そのトリガによって同じテーブルに対して INSERT ステートメントが実行された場合、INSTEAD OF トリガによって実行された INSERT ステートメントでは、そのトリガは再度呼び出されません。トリガによって INSERT が実行されると、制約動作の実行処理とそのテーブルに定義されている AFTER INSERT トリガの起動処理が開始されます。

ビューに定義された INSTEAD OF トリガによって、そのビューに対し、通常 INSTEAD OF トリガを再起動するステートメントが実行された場合、そのトリガの再帰呼び出しは行われません。代わりに、そのステートメントは、そのビューの基になるベース テーブルに対する変更として解決されます。この場合、ビューの定義では、更新可能なビューの制限をすべて満たしている必要があります。更新可能なビューの定義については、「ビューを使用したデータ変更」を参照してください。

たとえば、あるビューに INSTEAD OF UPDATE トリガが定義されており、そのトリガによって同じビューを参照する UPDATE ステートメントが実行された場合、INSTEAD OF トリガによって実行された UPDATE ステートメントでは、そのトリガは再度呼び出されません。トリガによって実行された UPDATE ステートメントでは、ビューに INSTEAD OF トリガが存在しないものとして処理が行われます。この UPDATE によって変更された列は、単一のベース テーブルに対して解決される必要があります。基になるベース テーブルを変更するたびに、制約の適用とそのテーブルに定義された AFTER トリガの起動が連鎖的に開始されます。

特定の列に対する UPDATE または INSERT 操作のテスト

特定の列に対する UPDATE または INSERT による変更に基づいて、操作を実行するよう Transact-SQL トリガを設定できます。これを行うには、トリガ内で UPDATE() または COLUMNS_UPDATED を使用します。UPDATE() では、1 つの列に対する UPDATE または INSERT の操作がテストされます。COLUMNS_UPDATED では、複数の列に対する UPDATE 操作や INSERT 操作がテストされ、どの列が挿入または更新されたかを示すビット パターンが返されます。

トリガの制限

CREATE TRIGGER はバッチ内の最初のステートメントとして使用する必要があり、1 つのテーブルにのみ適用されます。

トリガは現在のデータベース内でしか作成できませんが、他のデータベース内のオブジェクトを参照することができます。

トリガを修飾するトリガ スキーマ名を指定する場合は、テーブル名を同じ方法で修飾します。

1 つのトリガ動作を、同じ CREATE TRIGGER ステートメント内の複数のユーザー操作 (たとえば、INSERT と UPDATE) に対して定義できます。

INSTEAD OF DELETE/UPDATE トリガは、外部キーを持ち、DELETE/UPDATE 操作に対する連鎖操作が定義されているテーブルでは定義できません。

トリガの内部では任意の SET ステートメントを指定できます。選択した SET オプションは、トリガの実行中有効で、終了後は元の設定に戻ります。

トリガが起動すると、ストアド プロシージャの場合と同様に、呼び出し側アプリケーションに結果が返されます。トリガを実行しても結果がアプリケーションに返されないようにするには、結果を返す SELECT ステートメントや変数を割り当てるステートメントをトリガから除外します。ユーザーに結果を返す SELECT ステートメントや変数を割り当てるステートメントを含むトリガは、トリガ テーブルの変更が許可される各アプリケーションに結果を書き込む必要があるという点から、特殊な扱いが必要です。トリガ内で変数を割り当てる必要がある場合は、トリガの先頭で SET NOCOUNT ステートメントを使用して、結果セットが返されないようにします。

TRUNCATE TABLE ステートメントは実質的には DELETE ステートメントですが、個別の行の削除がログに記録されないため、トリガはアクティブになりません。ただし、TRUNCATE TABLE ステートメントを実行する権限のあるユーザー以外は、このように DELETE トリガが回避されてしまうことに注意を払う必要はありません。

ログに記録されるかどうかにかかわらず、WRITETEXT ステートメントによってトリガがアクティブになることはありません。

次の Transact-SQL ステートメントは DML トリガでは許可されません。

ALTER DATABASE	CREATE DATABASE	DROP DATABASE
LOAD DATABASE	LOAD LOG	RECONFIGURE
RESTORE DATABASE	RESTORE LOG

また、次の Transact-SQL ステートメントは、トリガによって起動される操作の対象となるテーブルまたはビューに対して使用する場合は、DML トリガ内では使用できません。

CREATE INDEX (CREATE SPATIAL INDEX および CREATE XML INDEX を含む)	ALTER INDEX	DROP INDEX
DBCC DBREINDEX	ALTER PARTITION FUNCTION	DROP TABLE
ALTER TABLE (次の操作で使用する場合) 列の追加、変更、または削除 パーティションの切り替え PRIMARY KEY 制約や UNIQUE 制約の追加または削除

注意
SQL Server ではシステム テーブルに対するユーザー定義トリガがサポートされないため、システム テーブルに対してはユーザー定義トリガを作成しないことをお勧めします。

DDL トリガ

DDL トリガでは、標準のトリガと同様、イベントに応答してストアド プロシージャが実行されますが、標準のトリガとは異なり、テーブルまたはビューの UPDATE、INSERT、DELETE ステートメントに応答して実行されることはありません。このトリガは、主にデータ定義言語 (DDL) ステートメントに応答して起動します。このようなステートメントには、CREATE、ALTER、DROP、GRANT、DENY、REVOKE、UPDATE STATISTICS ステートメントなどがあります。DDL と同様の操作を実行する特定のシステム ストアド プロシージャも DDL トリガを起動できます。

重要
DDL トリガをテストして、システム ストアド プロシージャの実行に対する応答を確認してください。たとえば、CREATE TYPE ステートメントおよび sp_addtype ストアド プロシージャと sp_rename ストアド プロシージャは、CREATE_TYPE イベントで作成される DDL トリガを起動します。

DDL トリガの詳細については、「DDL トリガ」を参照してください。

DDL トリガは、ローカルまたはグローバルの一時テーブルおよびストアド プロシージャに影響するイベントに応答して起動されることはありません。

DML トリガと異なり、DDL トリガのスコープはスキーマに設定されません。このため、DDL トリガに関するメタデータのクエリに、OBJECT_ID、OBJECT_NAME、OBJECTPROPERTY、OBJECTPROPERTYEX などの関数を使用することはできません。代わりに、カタログ ビューを使用してください。詳細については、「DDL トリガに関する情報の取得」を参照してください。

注意
サーバー スコープの DDL トリガは、SQL Server Management Studio オブジェクト エクスプローラの [Triggers] フォルダに表示されます。このフォルダは、[Server Objects] フォルダにあります。データベース スコープの DDL トリガは、[Database Triggers] フォルダに表示されます。このフォルダは対応するデータベースの [Programmability] フォルダにあります。

ログオン トリガ

ログオン トリガは、LOGON イベントに応答してストアド プロシージャを実行します。このイベントは、SQL Server インスタンスでユーザー セッションが確立されるときに発生します。ログオン トリガは、ログインの認証段階が終了した後、ユーザー セッションが実際に確立されるまでの間に発生します。したがって、通常、エラー メッセージや PRINT ステートメントからのメッセージはユーザーに通知されますが、このトリガ内で発生したすべてのメッセージは SQL Server のエラー ログに記録されます。詳細については、「ログオン トリガ」を参照してください。

認証に失敗した場合は、ログオン トリガが作動しません。

分散トランザクションはログオン トリガではサポートされていません。分散トランザクションが含まれているログオン トリガが起動されると、エラー 3969 が返されます。

トリガについての留意事項

結果を返す

今後のバージョンの SQL Server では、トリガを使用して結果を返す機能が削除される予定です。結果セットを返すトリガは、それと連動するように設計されていないアプリケーションでは予期しない動作を起こすことがあります。新しい開発作業では、トリガを使用して結果セットを返すことを避け、現在この方法を使用しているアプリケーションについては変更を検討してください。トリガが結果セットを返さないようにするには、disallow results from triggers オプションを 1 に設定します。

ログオン トリガは、結果セットを返すことを常に禁止しているため、この動作は構成できません。ログオン トリガが結果セットを生成すると、トリガは実行に失敗し、トリガを起動したログイン試行は拒否されます。

複数のトリガ

SQL Server では、各 DML イベント、DDL イベント、または LOGON イベントに対して複数のトリガを作成できます。たとえば、既に UPDATE トリガが作成されているテーブルに対して CREATE TRIGGER FOR UPDATE を実行すると、追加の更新トリガが作成されます。以前のバージョンの SQL Server では、各テーブルにおいて、INSERT、UPDATE、DELETE の各データ修正イベントに許可されるトリガは 1 つだけでした。

再帰トリガ

SQL Server では、ALTER DATABASE によって RECURSIVE_TRIGGERS 設定が有効になっていれば、トリガの再帰呼び出しが行えます。

再帰トリガでは、次の種類の再帰呼び出しが有効になります。

• 間接再帰

  間接再帰では、アプリケーションでテーブル 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 ステートメントのみに対応する行が格納されています。

注意
この動作は、ALTER DATABASE によって RECURSIVE_TRIGGERS 設定が有効になっている場合にのみ実行されます。あるイベントに対して複数のトリガが定義されている場合、それらの実行順序に決まりはありません。個々のトリガは自己完結している必要があります。

RECURSIVE_TRIGGERS の設定を無効にすると、直接再帰のみが無効になります。間接再帰呼び出しを無効にするには、sp_configure を使用して、サーバー オプション nested triggers を 0 に設定します。

いずれかのトリガで ROLLBACK TRANSACTION が実行されると、入れ子レベルにかかわらず、それ以降のトリガは実行されません。

入れ子にされたトリガ

トリガは 32 レベルまで入れ子にできます。トリガによって、別のトリガが存在するテーブルが変更された場合、この 2 番目のトリガがアクティブになり、さらに別のトリガを呼び出すことができます。この連鎖的なトリガで無限ループが発生すると、入れ子レベルを超過した時点でトリガはキャンセルされます。Transact-SQL トリガで、CLR ルーチン、データ型、または集計を参照することによってマネージ コードが実行された場合、この参照は 32 レベルの入れ子制限の 1 レベルとしてカウントされます。マネージ コード内から呼び出されたメソッドは、この制限としてはカウントされません。

入れ子にされたトリガを無効にするには、sp_configure の nested triggers オプションを 0 (オフ) に設定します。既定の構成では、入れ子にされたトリガは許可されています。入れ子にされたトリガがオフの場合、ALTER DATABASE によって RECURSIVE_TRIGGERS がどのように設定されていても、再帰トリガは無効になります。

注意
SQL Server 2000 では、INSTEAD OF トリガ内で入れ子になった AFTER トリガは、nested triggers サーバー構成オプションがオフになっていれば起動されません。SQL Server 2005 以降では、INSTEAD OF トリガ内で入れ子になっている最初の AFTER トリガは、nested triggers サーバー構成オプションが 0 に設定されていても起動されます。ただし、この設定では、後続の AFTER トリガは起動されません。アプリケーションに入れ子になったトリガがないかどうかを調査し、nested triggers サーバー構成オプションが 0 に設定されている場合の動作に関して、アプリケーションがビジネス ルールに従っているかどうかを判断し、その後、適切な変更を加えることをお勧めします。

名前の遅延解決

SQL Server では、Transact-SQL のストアド プロシージャ、トリガ、バッチで、コンパイル時に存在しないテーブルを参照できます。この機能を名前の遅延解決といいます。ただし、Transact-SQL のストアド プロシージャ、トリガ、またはバッチで、ストアド プロシージャまたはトリガで定義されているテーブルを参照する場合は、互換性レベルが 65 に設定されている場合にのみ作成時に警告が発生します。バッチが使用される場合は、コンパイル時に警告が発生します。参照されるテーブルが存在しない場合は、実行時にエラー メッセージが返されます。詳細については、「名前の遅延解決とコンパイル」を参照してください。

権限

DML トリガを作成するには、トリガを作成するテーブルまたはビューに対する ALTER 権限が必要です。

サーバー スコープ (ON ALL SERVER) の DDL トリガ、またはログオン トリガを作成するには、サーバーに対する CONTROL SERVER 権限が必要です。データベース スコープ (ON DATABASE) の DDL トリガを作成するには、現在のデータベースに対する ALTER ANY DATABASE DDL TRIGGER 権限が必要です。

例

A. DML トリガを事前通知と組み合わせて使用する

次の DML トリガでは、Customer テーブルでデータの追加または変更が試行されたときに、クライアントに対してメッセージを表示します。

USE AdventureWorks;
GO
IF OBJECT_ID ('Sales.reminder1', 'TR') IS NOT NULL
   DROP TRIGGER Sales.reminder1;
GO
-- This trigger raises a message whenever a row is inserted or modified in Sales.Customer.

CREATE TRIGGER reminder1
ON Sales.Customer
AFTER INSERT, UPDATE 
AS RAISERROR ('Notify Customer Relations', 16, 10);
GO

B. DML トリガを電子メール メッセージと組み合わせて使用する

次の例では、Customer テーブルが変更されたときに、指定したユーザー (MaryM) に電子メールを送信します。

USE AdventureWorks;
GO
IF OBJECT_ID ('Sales.reminder2','TR') IS NOT NULL
    DROP TRIGGER Sales.reminder2;
GO
-- This trigger sends an e-mail message to a specified account whenever
-- a row is inserted, updated or deleted from the Sales.Customer table.

CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE 
AS
   EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'AdventureWorks Administrator',
        @recipients = 'danw@Adventure-Works.com',
        @body = 'Don''t forget to print a report for the sales force.',
        @subject = 'Reminder';
GO

C. DML AFTER トリガを使用して、PurchaseOrderHeader テーブルと Vendor テーブルの間にビジネス ルールを設定する

CHECK 制約では、列レベルまたはテーブル レベルの制約が定義されている列だけを参照できるので、テーブル間にまたがる制約 (ここでは、ビジネス ルール) はすべてトリガとして定義する必要があります。

次の例では、DML トリガを作成します。このトリガでは、PurchaseOrderHeader テーブルに新しい発注を挿入しようとしたときに、ベンダの信用格付けが良好であるかどうかがチェックされます。ベンダの信用格付けを取得するには、Vendor テーブルを参照する必要があります。信用格付けが低い場合は、メッセージが表示され、挿入は実行されません。

IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
   DROP TRIGGER Purchasing.LowCredit;
GO
-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).

CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
IF EXISTS (SELECT *
           FROM Purchasing.PurchaseOrderHeader p 
           JOIN inserted AS i 
           ON p.PurchaseOrderID = i.PurchaseOrderID 
           JOIN Purchasing.Vendor AS v 
           ON v.VendorID = p.VendorID
           WHERE v.CreditRating = 5
          )
BEGIN
RAISERROR ('This vendor''s credit rating is too low to accept new
purchase orders.', 16, 1);
ROLLBACK TRANSACTION;
RETURN 
END;

-- This statement attempts to insert a row into the PurchaseOrderHeader table
-- for a vendor that has a below average credit rating.
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.

INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID,
VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)
VALUES(
2
,3
,261    
,1652   
,4  
,GETDATE()
,GETDATE()
,44594.55   
,3567.564   
,1114.8638);
GO

D. 名前の遅延解決を使用する

次の例では、2 つの DML トリガを作成し、名前の遅延解決の使用方法を示します。

USE AdventureWorks;
GO
IF OBJECT_ID ('HumanResources.trig1','TR') IS NOT NULL
   DROP TRIGGER HumanResources.trig1;
GO
-- Creating a trigger on a nonexistent table.
CREATE TRIGGER HumanResources.trig1
on HumanResources.Employee
AFTER INSERT, UPDATE, DELETE
AS 
   SELECT e.EmployeeID, e.BirthDate, x.info 
   FROM HumanResources.Employee AS e INNER JOIN does_not_exist AS x 
      ON e.EmployeeID = x.xID
GO

-- This statement displays the text of the trigger.
SELECT t.object_id, m.definition
FROM sys.triggers AS t INNER JOIN sys.sql_modules AS m 
   ON t.object_id = m.object_id
WHERE t.type = 'TR' and t.name = 'trig1'
AND t.parent_class = 1
GO

-- Creating a trigger on an existing table, but with a nonexistent 
-- column.
USE AdventureWorks;
GO
IF OBJECT_ID ('HumanResources.trig2','TR') IS NOT NULL
   DROP TRIGGER HumanResources.trig2
GO

CREATE TRIGGER HumanResources.trig2 
ON HumanResources.Employee
AFTER INSERT, UPDATE
AS 
   DECLARE @fax varchar(12)
   SELECT @fax = 'AltPhone'
   FROM HumanResources.Employee
GO

-- This statement displays the text of the trigger.

SELECT t.object_id, m.definition
FROM sys.triggers AS t INNER JOIN sys.sql_modules AS m 
   ON t.object_id = m.object_id
WHERE t.type = 'TR' and t.name = 'trig2'
AND t.parent_class = 1
GO

E. データベース スコープの DDL トリガを使用する

次の例では、DDL トリガを使用して、データベースのシノニムが削除されないようにします。

USE AdventureWorks;
GO

IF EXISTS (SELECT * FROM sys.triggers
    WHERE parent_class = 0 AND name = 'safety')
DROP TRIGGER safety
ON DATABASE;
GO

CREATE TRIGGER safety 
ON DATABASE 
FOR DROP_SYNONYM
AS 
   RAISERROR ('You must disable Trigger "safety" to drop synonyms!',10, 1)
   ROLLBACK
GO

DROP TRIGGER safety
ON DATABASE;
GO

F. サーバー スコープの DDL トリガを使用する

次の例では、DDL トリガを使用して、現在のサーバー インスタンスで CREATE DATABASE イベントが発生したときにメッセージを表示し、EVENTDATA 関数を使用して、対応する Transact-SQL ステートメントのテキストを取得します。

注意
DDL トリガで EVENTDATA を使用するその他の例については、「EVENTDATA 関数の使用」を参照してください。

IF EXISTS (SELECT * FROM sys.server_triggers
    WHERE name = 'ddl_trig_database')
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO

CREATE TRIGGER ddl_trig_database 
ON ALL SERVER 
FOR CREATE_DATABASE 
AS 
    PRINT 'Database Created.'
    SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO

DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO

G. ログオン トリガを使用する

次のログオン トリガの例では、login_test ログインで既に 3 つのユーザー セッションが実行されている場合に、そのログインのメンバとして SQL Server にログインを試行すると拒否されます。

USE master;
GO

CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,
    CHECK_EXPIRATION = ON;
GO

GRANT VIEW SERVER STATE TO login_test;
GO

CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'login_test') > 3
    ROLLBACK;
END;

H. トリガを起動するイベントを表示する

次の例では、sys.triggers および sys.trigger_events カタログ ビューをクエリし、どの Transact-SQL 言語イベントでトリガ safety が起動されるかを特定します。safety は前の例で作成したものです。

SELECT TE.*
FROM sys.trigger_events AS TE
JOIN sys.triggers AS T
ON T.object_id = TE.object_id
WHERE T.parent_class = 0
AND T.name = 'safety'
GO

変更履歴

変更内容
「解説」の「入れ子にされたトリガ」セクションに、SQL Server 2005 で導入された AFTER トリガが INSTEAD OF トリガ内に入れ子にされているときの動作の変更に関する注記を追加しました。
「ログオン トリガ」セクションに、これらのトリガでは分散トランザクションがサポートされないという情報を追加しました。
sp_rename ストアド プロシージャはどの DDL トリガも起動しないという不適切な記述を削除しました。

関連項目

参照

ALTER TABLE (Transact-SQL)

ALTER TRIGGER (Transact-SQL)

COLUMNS_UPDATED (Transact-SQL)

CREATE TABLE (Transact-SQL)

DROP TRIGGER (Transact-SQL)

ENABLE TRIGGER (Transact-SQL)

DISABLE TRIGGER (Transact-SQL)

TRIGGER_NESTLEVEL (Transact-SQL)

EVENTDATA (Transact-SQL)

sys.dm_sql_referenced_entities (Transact-SQL)

sys.dm_sql_referencing_entities (Transact-SQL)

sys.sql_expression_dependencies (Transact-SQL)

sp_help (Transact-SQL)

sp_helptrigger (Transact-SQL)

sp_helptext (Transact-SQL)

sp_rename (Transact-SQL)

sp_settriggerorder (Transact-SQL)

UPDATE() (Transact-SQL)

sys.triggers (Transact-SQL)

sys.trigger_events (Transact-SQL)

sys.sql_modules (Transact-SQL)

sys.assembly_modules (Transact-SQL)

sys.server_triggers (Transact-SQL)

sys.server_trigger_events (Transact-SQL)

sys.server_sql_modules (Transact-SQL)

sys.server_assembly_modules (Transact-SQL)

概念

CLR トリガのプログラミング

オブジェクト名としての識別子の使用

DML トリガに関する情報の取得

DDL トリガに関する情報の取得

NOT FOR REPLICATION を使用した制約、ID、およびトリガの制御

大きな値のデータ型の使用