CREATE TRIGGER (Transact-SQL)

DML トリガー、DDL トリガー、またはログオン トリガーを作成します。 トリガーとは、特殊な種類のストアド プロシージャであり、データベース サーバーでイベントが発生したときに自動的に実行されます。 DML トリガーは、ユーザーがデータ操作言語 (DML) イベントを介してデータを変更しようとしたときに実行されます。 DML イベントは、テーブルやビューに対する INSERT、UPDATE、または DELETE ステートメントによって発生するイベントです。 これらのトリガーは、テーブル行が影響を受けるかどうかにかかわらず、有効なイベントが発生したときに起動されます。 詳細については、「DML トリガー」を参照してください。

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

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

適用対象: SQL Server (SQL Server 2008 から現在のバージョンまで)、Windows Azure SQL データベース (初回のリリースから現在のバージョンまで)

Transact-SQL 構文表記規則

構文

-- SQL Server Syntax 
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 ]

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 ]

構文

-- Windows Azure SQL Database Syntax  
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 ] } 
  AS { sql_statement  [ ; ] [ ,...n ] [ ; ] > }

<dml_trigger_option> ::= 
        [ EXECUTE AS Clause ] 

-- Windows Azure SQL Database Syntax
Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger) 

CREATE TRIGGER trigger_name 
ON { DATABASE } 
 [ WITH <ddl_trigger_option> [ ,...n ] ] 
{ FOR | AFTER } { event_type | event_group } [ ,...n ] 
AS { sql_statement  [ ; ] [ ,...n ]  [ ; ] }

<ddl_trigger_option> ::= 
    [ EXECUTE AS Clause ]

引数

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

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

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

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

• ALL SERVER

  適用対象: SQL Server 2008 から SQL Server 2014

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

• WITH ENCRYPTION

  適用対象: SQL Server 2008 から SQL Server 2014

  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 オプションを指定できません。

• WITH APPEND

  適用対象: SQL Server 2008 から SQL Server 2008 R2

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

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

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

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

• NOT FOR REPLICATION

  適用対象: SQL Server 2008 から SQL Server 2014

  トリガーに関係するテーブルがレプリケーション エージェントによって変更されるときに、トリガーを実行しないことを示します。

• 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 の各データ型は、将来のバージョンの Microsoft SQL Server で削除される予定です。新しい開発作業では、これらのデータ型の使用は避け、現在これらのデータ型を使用しているアプリケーションは修正するようにしてください。代わりに、nvarchar(max)、varchar(max)、varbinary(max) を使用してください。AFTER トリガーと INSTEAD OF トリガーでは両方とも、inserted および deleted テーブルの varchar(MAX)、nvarchar(MAX)、および varbinary(MAX) 型のデータがサポートされます。

• < method_specifier >

  適用対象: SQL Server 2008 から SQL Server 2014

  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 ステートメントが正常に実行された後にのみ実行されます。 このステートメントの実行には、更新または削除されるオブジェクトに関連付けられている連鎖的なすべての参照操作と制約チェックの実行も含まれます。 AFTER トリガーは、同じテーブルで INSTEAD OF トリガーを再帰的に起動することはありません。

テーブルに定義された 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
RESTORE DATABASE	RESTORE LOG	RECONFIGURE

また、次の 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 が返されます。

ログオン トリガーを無効にする

ログオン トリガーを使用すると、sysadmin 固定サーバー ロールのメンバーを含むすべてのユーザーのデータベース エンジンへの接続を効率的に禁止できます。 ログオン トリガーによって接続が禁止されているときでも、sysadmin 固定サーバー ロールのメンバーは、専用管理者接続を使用するか、またはデータベース エンジンを最小構成モード (-f) で起動することにより、接続できます。 詳細については、「データベース エンジン サービスのスタートアップ オプション」を参照してください。

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

結果の返送

今後のバージョンの 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 が更新されます。 この再帰呼び出しでは、次にトリガー T2 が起動し、テーブル 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 (オフ) に設定します。 既定の構成では、入れ子になったトリガーは許可されています。 nested triggers がオフの場合、ALTER DATABASE によって RECURSIVE_TRIGGERS がどのように設定されていても、recursive triggers も無効になります。

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

名前の遅延解決

SQL Server では、Transact-SQL のストアド プロシージャ、トリガー、バッチで、コンパイル時に存在しないテーブルを参照できます。 この機能を名前の遅延解決といいます。

権限

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

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

使用例

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

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

IF OBJECT_ID ('Sales.reminder1', 'TR') IS NOT NULL
   DROP TRIGGER Sales.reminder1;
GO
CREATE TRIGGER reminder1
ON Sales.Customer
AFTER INSERT, UPDATE 
AS RAISERROR ('Notify Customer Relations', 16, 10);
GO

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

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

IF OBJECT_ID ('Sales.reminder2','TR') IS NOT NULL
    DROP TRIGGER Sales.reminder2;
GO
CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE 
AS
   EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'AdventureWorks2012 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 トリガーを AdventureWorks2012 データベースに作成します。 このトリガーでは、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 AS p 
           JOIN inserted AS i 
           ON p.PurchaseOrderID = i.PurchaseOrderID 
           JOIN Purchasing.Vendor AS v 
           ON v.BusinessEntityID = p.VendorID
           WHERE v.CreditRating = 5
          )
BEGIN
RAISERROR ('A vendor''s credit rating is too low to accept new
purchase orders.', 16, 1);
ROLLBACK TRANSACTION;
RETURN 
END;
GO

-- 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. データベース スコープの DDL トリガーを使用する

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

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

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

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

適用対象: SQL Server 2008 から SQL Server 2014

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

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

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

適用対象: SQL Server 2008 から SQL Server 2014

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;

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

次の例は、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

関連項目

参照

ALTER TABLE (Transact-SQL)

ALTER TRIGGER (Transact-SQL)

COLUMNS_UPDATED (Transact-SQL)

CREATE TABLE (SQL Server)

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)

概念

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

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