CREATE TRIGGER (Transact-SQL)

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

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

DDL トリガーは、さまざまなデータ定義言語 (DDL) イベントに応答して実行されます。 これらのイベントは、主に、Transact-SQL CREATE、 ALTER、および DROP ステートメント、および DDL に似た操作を実行する特定のシステム ストアド プロシージャに対応します。

ログオン トリガーは、ユーザーのセッションが確立されるときに発生する LOGON イベントに応答して発生します。 トリガーは、Transact-SQL ステートメントから直接作成することも、Microsoft .NET Framework 共通言語ランタイム (CLR) 内に作成したアセンブリのメソッドから作成し、SQL Server のインスタンスにアップロードすることもできます。 SQL Server では、特定のステートメントに対して複数のトリガーを作成できます。

Transact-SQL 構文表記規則

構文

SQL Server 構文

テーブルまたはビューに対する INSERT、 UPDATE、または DELETE ステートメントに対するトリガー (DML トリガー):

CREATE [ OR ALTER ] 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

テーブルへの INSERT、 UPDATE、または DELETE ステートメントに対するトリガー (メモリ最適化テーブルの DML トリガー):

CREATE [ OR ALTER ] TRIGGER [ schema_name . ] trigger_name
ON { table }
[ WITH <dml_trigger_option> [ , ...n ] ]
{ FOR | AFTER }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS { sql_statement  [ ; ] [ , ...n ] }

<dml_trigger_option> ::=
    [ NATIVE_COMPILATION ]
    [ SCHEMABINDING ]
    [ EXECUTE AS Clause ]

CREATE、ALTER、DROP、GRANT、DENY、REVOKE、または UPDATE ステートメント (DDL トリガー) でトリガーします。

CREATE [ OR ALTER ] 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 ]

LOGON イベントのトリガー (ログオン トリガー):

CREATE [ OR ALTER ] 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 ]

Azure SQL Database の構文

テーブルまたはビューに対する INSERT、 UPDATE、または DELETE ステートメントに対するトリガー (DML トリガー):

CREATE [ OR ALTER ] 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 ]

CREATE、ALTER、DROP、GRANT、DENY、REVOKE、または UPDATE STATISTICS ステートメント (DDL トリガー) でトリガーします。

CREATE [ OR ALTER ] 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 ]

引数

または変更

適用対象: SQL Server 2016 (13.x) SP1 以降のバージョン、および Azure SQL Database

トリガーが既に存在する場合にのみ、条件付きでビューを変更します。

schema_name

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

trigger_name

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

table | 眺める

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

データベース

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 句」を参照してください。

ネイティブコンパイル

トリガーをネイティブでコンパイルすることを示します。

このオプションは、メモリ最適化テーブルのトリガーに必要です。

SCHEMABINDING

トリガーによって参照されているテーブルを削除または変更できないようにします。

このオプションは、メモリ最適化テーブルのトリガーで必要であり、従来のテーブルのトリガーではサポートされていません。

FOR |後

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

ビューで AFTER トリガーを定義することはできません。

代わりに

トリガーをアクティブにする SQL ステートメントの "代わりに" DML トリガーを起動するように指定します。したがって、トリガーをアクティブにするステートメントの操作は無効になります。 DDL トリガーまたはログオン トリガーに INSTEAD OF を指定することはできません。

最大で、テーブルまたはビューのINSERT、UPDATE、またはDELETEステートメントごとに 1 つのINSTEAD OF トリガーを定義できます。 また、各ビューに独自の INSTEAD OF トリガーがあるビューに対してビューを定義することもできます。

WITH CHECK OPTIONを使用する更新可能なビューでINSTEAD OFトリガーを定義することはできません。 これにより、 INSTEAD OF トリガーが更新可能なビューに追加されたときにエラー WITH CHECK OPTION 発生します。 INSTEAD OF トリガーを定義する前に、ALTER VIEWを使用してそのオプションを削除します。

{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }

テーブルまたはビューに対して試行されたときに、DML トリガーをアクティブにするデータ変更ステートメントを指定します。 少なくとも 1 つのオプションを指定します。 これらのオプションを任意の順序で組み合わせて、トリガー定義内で使用できます。

INSTEAD OF トリガーの場合、参照リレーションシップを持つテーブルでDELETE オプションを使用して、連鎖アクション ON DELETEを指定することはできません。 同様に、 UPDATE オプションは、参照リレーションシップを持つテーブルでは許可されず、連鎖アクション ON UPDATEを指定します。

WITH APPEND

適用対象: SQL Server 2008 (10.0.x) から SQL Server 2008 R2 (10.50.x)。

既存のトリガーに対して、新しいトリガーを追加します。 WITH APPEND は、 INSTEAD OF トリガーや、 AFTER トリガーが明示的に指定されている場合には使用できません。 下位互換性のために、INSTEAD OFやAFTERを使用せずに、FORが指定されている場合にのみWITH APPENDを使用します。 EXTERNAL NAMEを使用している場合 (つまり、トリガーが CLR トリガーの場合) は、WITH APPENDを指定できません。

event_type

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

event_group

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

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

レプリケーション用ではありません

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

sql_statement

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

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

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

DML トリガーでは、deleted および inserted 論理 (概念) テーブルが使用されます。 それらは、トリガーが定義されるテーブル、つまり、ユーザー操作の対象となるテーブルと構造的に類似しています。 削除されたテーブルと挿入されたテーブルには、ユーザー アクションによって変更される可能性がある行の古い値または新しい値が保持されます。 たとえば、deleted テーブルのすべての値を取得するには、次のように指定します。

SELECT * FROM deleted;

詳細については、「 挿入テーブルと削除テーブルを使用する」を参照してください。

DDL トリガーとログオン トリガーは、 EVENTDATA 関数を使用してトリガーイベントに関する情報をキャプチャします。 詳しくは、「EVENTDATA 関数の使用」をご覧ください。

SQL Server では、テーブルまたはビューのINSTEAD OF トリガーを使用して、テキスト、n テキスト、またはイメージの列を更新できます。

メモリ最適化テーブルのトリガーの場合、最上位レベルで許可される sql_statement は ATOMIC ブロックのみです。 ATOMIC ブロック内で許可される T-SQL は、ネイティブ プロシージャ内で許可される T-SQL によって制限されます。

<method_specifier>

CLR トリガーに対して、トリガーにバインドするアセンブリのメソッドを指定します。 このメソッドは引数を受け取らず、void を返す必要があります。 class_name は有効な 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 トリガーは起動されません。

sp_settriggerorderを使用して、テーブルで実行するトリガーの最初と最後のAFTERを指定できます。 テーブルに対するINSERT、UPDATE、およびDELETE操作ごとに、最初と最後の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によって変更された列は、1 つのベース テーブルに解決する必要があります。 基になるベース テーブルに対する各変更により、制約を適用し、テーブルに対して定義されたトリガー AFTER 発生するチェーンが開始されます。

特定の列に対する UPDATE アクションまたは INSERT アクションをテストする

特定の列に対する UPDATE または INSERT の変更に基づいて特定のアクションを実行するように、Transact-SQL トリガーを設計できます。 この目的のために、トリガーの本体で UPDATE または COLUMNS_UPDATED を使用します。 UPDATE() は、1 つの列で UPDATE または INSERT 試行をテストします。 COLUMNS_UPDATED は、複数の列で実行される UPDATE または INSERT アクションをテストします。 この関数は、挿入または更新された列を示すビット パターンを返します。

トリガーの制限事項

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

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

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

同じCREATE TRIGGER ステートメント内の複数のユーザー アクション (INSERTやUPDATEなど) に対して、同じトリガー アクションを定義できます。

INSTEAD OF DELETE / INSTEAD OF 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
• DROP TABLE
• DBCC DBREINDEX
• ALTER PARTITION FUNCTION
• ALTER TABLE 次のアクションを実行するために使用する場合:
  • 列の追加、変更、または削除
  • パーティションの切り替え
  • PRIMARY KEY制約またはUNIQUE制約を追加または削除します。

DML トリガーを最適化する

トリガーは、トランザクションで (黙示的に、またはそれ以外の方法で) 機能し、開いている間はリソースをロックします。 ロックは、トランザクションが確認 ( COMMIT) または拒否 ( ROLLBACK) されるまで保持されます。 トリガーの実行時間が長くなるほど、別のプロセスがブロックされる可能性が高くなります。 そのため、トリガーは、可能な限り継続時間が短くなるように記述します。 短い継続時間を実現するための 1 つの方法は、DML ステートメントによって変更される行が 0 行のときに、トリガーを解放することです。

どの行も変更しないコマンドでトリガーを解放するには、システム変数 ROWCOUNT_BIG を使用します。

次の T-SQL コード スニペットは、行を変更しないコマンドでトリガーを解放する方法を示しています。 このコードは、各 DML トリガーの先頭に存在する必要があります。

IF (ROWCOUNT_BIG() = 0)
RETURN;

DDL トリガーの備考

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

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

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

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

ログオン トリガー

ログオン トリガーは、 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を実行し、1 回TR2します。 さらに、 TR1 の起動によって、 TR1 と TR2の実行が (再帰的に) トリガーされます。 特定のトリガーの挿入テーブルと削除されたテーブルには、トリガーを呼び出した UPDATE ステートメントにのみ対応する行が含まれています。

RECURSIVE_TRIGGERS設定を無効にすると、直接再帰のみが防止されます。 間接再帰も無効にするには、 sp_configureを使用して入れ子になったトリガー サーバー オプションを 0 に設定します。

入れ子レベルに関係なく、いずれかのトリガーが ROLLBACK TRANSACTIONを実行した場合、それ以上トリガーは実行されません。

入れ子になったトリガー

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

入れ子になったトリガーを無効にするには、 sp_configure の入れ子になったトリガー オプションを 0 (オフ) に設定します。 既定の構成では、入れ子になったトリガーがサポートされます。 入れ子になったトリガーがオフの場合、ALTER DATABASEを使用して設定されたRECURSIVE_TRIGGERS設定にもかかわらず、再帰トリガーも無効になります。

INSTEAD OF トリガー内に入れ子になった最初のAFTER トリガーは、入れ子になったトリガー サーバー構成オプションが 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 アクセス許可が必要です。

例

Ａ。 アラーム メッセージで DML トリガーを使用する

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

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

B. リマインダーの電子メール メッセージで DML トリガーを使用する

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

CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE
AS
    EXECUTE msdb.dbo.sp_send_dbmail
        @profile_name = 'AdventureWorks2022 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 トリガーを AdventureWorks2022 データベースに作成します。 このトリガーでは、PurchaseOrderHeader テーブルに新しい発注を挿入しようとしたときに、ベンダーの信用格付けが良好であるかどうか (5 ではない) がチェックされます。 ベンダーの信用格付けを取得するには、Vendor テーブルを参照する必要があります。 信用格付けが低い場合は、メッセージが表示され、挿入は発生されません。

USE AdventureWorks2022;
GO

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 (ROWCOUNT_BIG() = 0)
    RETURN;
    IF EXISTS (SELECT 1
        FROM inserted AS i
        INNER JOIN Purchasing.Vendor AS v
            ON v.BusinessEntityID = i.VendorID
            WHERE v.CreditRating = 5)
BEGIN
    RAISERROR ('A vendor''s credit rating is too low to accept new purchase orders.', 16, 1);
    ROLLBACK;
    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 トリガーを使用して、データベースのシノニムが削除されないようにします。

CREATE TRIGGER safety
    ON DATABASE
    FOR DROP_SYNONYM
    AS IF (@@ROWCOUNT = 0)
           RETURN;
       RAISERROR ('You must disable Trigger "safety" to remove synonyms!', 10, 1);
       ROLLBACK;
GO

DROP TRIGGER safety
    ON DATABASE;
GO

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

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

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. ログオン トリガーを使用する

次のログオン トリガーの例では、そのログインで既に 3 つのユーザー セッションが実行されている場合、 login_test ログインのメンバーとして SQL Server へのログインの試行を拒否します。 <password>を強力なパスワードに変更します。

USE master;
GO

CREATE LOGIN login_test
    WITH PASSWORD = '<password>' 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は、例 D で作成されます。 データベース スコープの DDL トリガーを使用します。

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

関連コンテンツ

• テーブル変更 (Transact-SQL)
• トリガーを変更 (Transact-SQL)
• COLUMNS_UPDATED (Transact-SQL)
• テーブルを作成 (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
• sys.dm_sql_referencing_entities
• sys.sql_expression_dependencies
• sp_help
• sp_helptrigger
• sp_helptext
• sp_rename
• sp_settriggerorder
• UPDATE - トリガー関数 (Transact-SQL)
• DML トリガーに関する情報の取得
• DDL トリガーに関する情報の取得
• sys.triggers
• sys.trigger_events
• sys.sql_modules
• sys.assembly_modules
• sys.server_triggers
• sys.server_trigger_events
• sys.server_sql_modules
• sys.server_assembly_modules