次の方法で共有

CREATE TRIGGER (Transact-SQL)

  • [アーティクル]

更新 : 2006 年 7 月 17 日

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

ms189799.note(ja-jp,SQL.90).gifメモ :
これらのトリガは、テーブル行が影響を受けるかどうかにかかわらず、有効なイベントが発生したときに起動されます。これは仕様による結果です。

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

ms189799.security(ja-jp,SQL.90).gifセキュリティ メモ :
上位の特権の下では、トリガ内の悪意のあるコードを実行できます。この脅威を緩和する方法の詳細については、「トリガのセキュリティの管理」を参照してください。

トピック リンク アイコン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 ステートメントで指定したすべての処理が正常に実行された場合のみ、トリガが起動されます。このトリガの実行前には、連鎖して行われるすべての参照操作と制約チェックも完了している必要があります。

    指定したキーワードが 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 トリガに使用される DDL イベント」を参照してください。

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

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

  • WITH APPEND
    既存のトリガに対して、新しいトリガを追加します。この句は省略可能です。使用する必要があるのは、互換性レベルが 65 以下の場合だけです。互換性レベルが 70 以上の場合は、既存のトリガにトリガを追加するとき、WITH APPEND 句を指定する必要はありません。互換性レベルが 70 以上の場合、既存のトリガに対するトリガの追加は、CREATE TRIGGER の既定の動作であるためです。詳細については、「sp_dbcmptlevel (Transact-SQL)」を参照してください。

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

    ms189799.note(ja-jp,SQL.90).gif重要 :
    WITH APPEND は今後のバージョンの Microsoft SQL Server では削除される予定です。新しい開発作業では、WITH APPEND の使用は避け、現在このオプションを使用しているアプリケーションは修正するようにしてください。

  • 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 関数の使用」を参照してください。

    互換性レベルが 70 に設定されている場合、SQL Server では、DELETE、INSERT、または UPDATE トリガで inserted および deleted テーブルの text、ntext、または image 列を参照することは許可されません。inserted および deleted テーブルの text 値、ntext 値、および image 値にはアクセスできません。INSERT トリガまたは UPDATE トリガで新しい値を取得するには、inserted テーブルを元の更新テーブルと結合します。互換性レベルが 65 以下のとき、inserted テーブルや deleted テーブルの text、ntext、image 型の列に対しては、NULL 値が許容される場合には NULL 値が返され、NULL 値が許容されない場合は長さ 0 の文字列が返されます。

    互換性レベルが 80 以上の場合、SQL Server では、テーブルやビューの INSTEAD OF トリガによって text、ntext、または image 型の列を更新できます。

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

    ms189799.note(ja-jp,SQL.90).gifメモ :
    既定では、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 トリガを TRUNCATE TABLE ステートメントと共に使用しないよう注意する必要があります。

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

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

ALTER DATABASE

CREATE DATABASE

DROP DATABASE

LOAD DATABASE

LOAD LOG

RECONFIGURE

RESTORE DATABASE

RESTORE LOG

 

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

ms189799.note(ja-jp,SQL.90).gif重要 :
この制約は SQL Server 2005 で導入されたものですが、旧バージョンとの互換性モードを 80 に設定した場合にも適用されます。

CREATE INDEX

ALTER INDEX

DROP INDEX

DBCC DBREINDEX

ALTER PARTITION FUNCTION

DROP TABLE

ALTER TABLE (次の操作で使用する場合)

  • 列の追加、変更、または削除
  • パーティションの切り替え
  • PRIMARY KEY 制約や UNIQUE 制約の追加または削除

 

 
ms189799.note(ja-jp,SQL.90).gifメモ :
SQL Server ではシステム テーブルに対するユーザー定義トリガがサポートされないため、システム テーブルに対してはユーザー定義トリガを作成しないことをお勧めします。

DDL トリガ

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

ms189799.note(ja-jp,SQL.90).gif重要 :
DDL トリガをテストして、システム ストアド プロシージャの実行に対する応答を確認してください。たとえば、CREATE TYPE ステートメントおよび sp_addtype ストアド プロシージャはどちらも、CREATE_TYPE イベントで作成される DDL トリガを起動します。ただし、sp_rename ストアド プロシージャは、どの DDL トリガも起動しません。

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

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

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

ms189799.note(ja-jp,SQL.90).gifメモ :
サーバー スコープの DDL トリガは、SQL Server Management Studio オブジェクト エクスプローラの [Triggers] フォルダに表示されます。このフォルダは、[Server Objects] フォルダにあります。データベース スコープの DDL トリガは、[Database Triggers] フォルダに表示されます。このフォルダは対応するデータベースの [Programmability] フォルダにあります。

ログオン トリガ

ログオン トリガは、LOGON イベントに応答して起動されます。このイベントは、ユーザー セッションが確立されるときに発生します。詳細については、「ログオン トリガ」を参照してください。

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

結果の返却

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

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

複数のトリガ

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

ms189799.note(ja-jp,SQL.90).gifメモ :
互換性レベルが 70 のとき、CREATE TRIGGER の既定の動作によって、トリガ名が異なる場合に既存のトリガに新しいトリガが追加されます。トリガ名が同じ場合は、SQL Server ではエラー メッセージが返されます。ただし、互換性レベルが 65 以下の場合、CREATE TRIGGER ステートメントで作成される新しいトリガは、トリガ名が異なる場合でも、同じ種類の既存のトリガと置き換わります。詳細については、「sp_dbcmptlevel (Transact-SQL)」を参照してください。

再帰トリガ

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 ステートメントのみに対応する行が格納されています。

ms189799.note(ja-jp,SQL.90).gifメモ :
この動作は、ALTER DATABASE によって RECURSIVE_TRIGGERS 設定が有効になっている場合にのみ実行されます。あるイベントに対して複数のトリガが定義されている場合、それらの実行順序に決まりはありません。個々のトリガは自己完結している必要があります。

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

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

入れ子にされたトリガ

トリガは 32 レベルまで入れ子にできます。トリガによって、別のトリガが存在するテーブルが変更された場合、この 2 番目のトリガがアクティブになり、さらに別のトリガを呼び出すことができます。この連鎖的なトリガで無限ループが発生すると、入れ子レベルを超過した時点でトリガはキャンセルされます。入れ子にされたトリガを無効にするには、sp_configure の nested triggers オプションを 0 (オフ) に設定します。既定の構成では、入れ子にされたトリガは許可されています。入れ子にされたトリガがオフの場合、ALTER DATABASE によって RECURSIVE_TRIGGERS がどのように設定されていても、再帰トリガは無効になります。

ms189799.note(ja-jp,SQL.90).gifメモ :
Transact-SQL トリガで、CLR ルーチン、データ型、または集計を参照することによってマネージ コードが実行された場合、この参照は 32 レベルの入れ子制限の 1 レベルとしてカウントされます。マネージ コード内から呼び出されたメソッドは、この制限としてはカウントされません。

名前の遅延解決

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
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
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 テーブルを参照する必要があります。信用格付けが低い場合は、メッセージが表示され、挿入は実行されません。

ms189799.note(ja-jp,SQL.90).gifメモ :
複数の行を更新する DML AFTER トリガの例については、「DML トリガの複数行に関する注意点」を参照してください。DML INSTEAD OF INSERT トリガの例については、「INSTEAD OF INSERT トリガ」を参照してください。 
IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
   DROP TRIGGER Purchasing.LowCredit;
GO
CREATE TRIGGER LowCredit ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
DECLARE @creditrating tinyint,
   @vendorid int
SELECT @creditrating = v.CreditRating, @vendorid = p.VendorID
FROM Purchasing.PurchaseOrderHeader AS p 
    INNER JOIN inserted AS i ON p.PurchaseOrderID =
   i.PurchaseOrderID 
    JOIN Purchasing.Vendor AS v on v.VendorID = i.VendorID
IF @creditrating = 5
BEGIN
   RAISERROR ('This vendor''s credit rating is too low to accept new
      purchase orders.', 16, 1)
ROLLBACK TRANSACTION
END
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
-- Here is the statement to actually see 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
-- Here is the statement to actually see 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 ステートメントのテキストを取得します。

ms189799.note(ja-jp,SQL.90).gifメモ :
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

参照

関連項目

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)
sp_depends (Transact-SQL)
sys.sql_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
sys.server_trigger_events
sys.server_sql_modules
sys.server_assembly_modules (Transact-SQL)

その他の技術情報

ストアド プロシージャの作成 (データベース エンジン)
CLR トリガのプログラミング
オブジェクト名としての識別子の使用
DML トリガに関する情報の取得
DDL トリガに関する情報の取得
NOT FOR REPLICATION を使用した制約、ID、およびトリガの制御
大きな値のデータ型の使用

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

変更履歴

リリース 履歴

2006 年 12 月 12 日
追加内容 :
  • SQL Server 2005 Service Pack 2 で導入されたログオン トリガの情報を全面的に追加。

2006 年 7 月 17 日
変更内容 :
  • 例 E と例 F を更新。

2006 年 4 月 14 日
追加内容 :
  • 「解説」に、DDL トリガをテストして、ストアド プロシージャの実行に対する応答を確認することを推奨する重要な注を追加。
  • 例 C に注を追加し、複数の行を更新する DML AFTER トリガの例と DML INSTEAD OF INSERT トリガの例へのリンクを記載。
  • 例 F に注を追加し、DDL トリガで EVENTDATA を使用する例へのリンクを記載。
変更内容 :
  • データベース メールを使用するよう例 B を更新。

2005 年 12 月 5 日
追加内容 :
  • DML トリガはローカルまたはグローバルの一時テーブルに対して定義できないことを追加。
  • DDL トリガは、ローカルまたはグローバルの一時テーブルおよびストアド プロシージャに影響するイベントに応答して起動されないことを追加。