CREATE TRIGGER (Transact-SQL)

[アーティクル]
04/26/2023

適用対象:SQL Server Azure SQL Database Azure 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 では、特定のステートメントに対して複数のトリガーを作成できます。

重要

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

Note

この記事では、SQL Server への .NET Framework CLR の統合について説明します。 CLR 統合は、Azure SQL Database には適用されません。

Transact-SQL 構文表記規則

SQL Server 構文

-- SQL Server Syntax  
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)  
  
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

-- SQL Server Syntax  
-- Trigger on an INSERT, UPDATE, or DELETE statement to a 
-- table (DML Trigger on memory-optimized tables)  
  
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 ]

-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, 
-- REVOKE or UPDATE statement (DDL Trigger)  
  
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 ]

-- Trigger on a LOGON event (Logon Trigger)  
  
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 構文

-- Azure SQL Database Syntax   
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)  
  
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 ]

-- Azure SQL Database Syntax  
-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, 
-- REVOKE, or UPDATE STATISTICS statement (DDL Trigger)   
  
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 ]

Note

SQL Server 2014 (12.x) 以前のバージョンの Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。

引数

OR ALTER
適用対象:Azure SQL Database、SQL Server (SQL Server 2016 (13.x) SP1 以降)。

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

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 (10.0.x) 以降。

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

WITH ENCRYPTION
適用対象: SQL Server 2008 (10.0.x) 以降。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

INSTEAD OF トリガーでは、ON DELETE 連鎖操作を指定する参照関係があるテーブルで、DELETE オプションを使用することはできません。同様に、ON UPDATE 連鎖操作を指定する参照関係があるテーブルで、UPDATE オプションを使用することはできません。

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

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

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 が終了した後でマクロとしても動作します。

NOT FOR REPLICATION
適用対象: SQL Server 2008 (10.0.x) 以降。

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

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) 型のデータがサポートされます。

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

< method_specifier >適用対象: SQL Server 2008 (10.0.x) 以降。

CLR トリガーに対して、トリガーにバインドするアセンブリのメソッドを指定します。このメソッドは引数を受け取らず、void を返す必要があります。 class_name は有効な SQL Server 識別子であり、アセンブリ内にアセンブリで可視のクラスとして存在している必要があります。このクラスの名前が名前空間で修飾されており、名前空間の部分がピリオド (.) で分けられている場合は、このクラス名を角かっこ ([ ]) または引用符 (" ") で区切る必要があります。入れ子になったクラスは使用できません。

Note

既定では、CLR コードを実行する SQL Server の機能はオフになっています。マネージドコードモジュールを参照するデータベースオブジェクトを作成、変更、削除することはできますが、sp_configure によって clr enabled オプションが有効になっていない場合、これらの参照は 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 操作のテスト

Transact-SQL トリガーは、特定の列に対する UPDATE または INSERT による変更に基づいて特定の操作を実行するように設定できます。これを行うには、トリガー内で 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
DROP TABLE
DBCC DBREINDEX
ALTER PARTITION FUNCTION
ALTER TABLE (次の操作で使用する場合):
- 列の追加、変更、または削除
- パーティションの切り替え
- PRIMARY KEY 制約や UNIQUE 制約の追加または削除

Note

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

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

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

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

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

Note

サーバースコープの DDL トリガーは、SQL Server Management Studio のオブジェクトエクスプローラーの [Triggers] フォルダーに表示されます。このフォルダーは、 [Server Objects] フォルダーにあります。データベーススコープの DDL トリガーは、 [データベーストリガー] フォルダーに表示されます。このフォルダーは対応するデータベースの [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 ステートメントのみに対応する行が格納されています。

Note

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

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

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

入れ子になったトリガー

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

入れ子にされたトリガーを無効にするには、sp_configure の nested triggers オプションを 0 (オフ) に設定します。既定の構成では、入れ子になったトリガーがサポートされます。入れ子になったトリガーがオフの場合、ALTER DATABASE によって 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 トリガーは、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  
   EXEC 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   
           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 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 トリガーを使用して、データベースのシノニムが削除されないようにします。

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

適用対象: SQL Server 2008 (10.0.x) 以降。

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 (10.0.x) 以降。

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 は、上の例 "D" で作成されます。

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)
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)
DML トリガーに関する情報の取得
 DDL トリガーに関する情報の取得
 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)

CREATE TRIGGER (Transact-SQL)

SQL Server 構文

Azure SQL Database 構文

引数

DML トリガーの解説

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

トリガーの制限

DML トリガーの最適化

DDL トリガーの解説

ログオントリガー

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

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

結果の返送

複数のトリガー

再帰トリガー

入れ子になったトリガー

名前の遅延解決

アクセス許可

例

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

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

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

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

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

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

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

参照

フィードバック

フィードバック

その他のリソース

CREATE TRIGGER (Transact-SQL)

SQL Server 構文

Azure SQL Database 構文

引数

DML トリガーの解説

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

トリガーの制限

DML トリガーの最適化

DDL トリガーの解説

ログオン トリガー

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

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

結果の返送

複数のトリガー

再帰トリガー

入れ子になったトリガー

名前の遅延解決

アクセス許可

例

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

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

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

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

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

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

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

参照

フィードバック

フィードバック

その他のリソース

ログオントリガー

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

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

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

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

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

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