[アーティクル]
04/01/2012

ALTER TRIGGER (Transact-SQL)

以前に CREATE TRIGGER ステートメントで作成された DML トリガー、DDL トリガー、またはログオントリガーの定義を変更します。トリガーは、CREATE TRIGGER を使用して作成します。これらのトリガーは、Transact-SQL ステートメントから直接作成することも、Microsoft .NET Framework 共通言語ランタイム (CLR) 内に作成したアセンブリのメソッドから作成し、SQL Server のインスタンスにアップロードすることもできます。ALTER TRIGGER ステートメントで使用されるパラメーターの詳細については、「CREATE TRIGGER (Transact-SQL)」を参照してください。

トピックリンクアイコン Transact-SQL 構文表記規則

構文

Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)
ALTER TRIGGER schema_name.trigger_name 
ON ( table | view ) 
[ WITH <dml_trigger_option> [ ,...n ] ]
(FOR | AFTER | INSTEAD OF ) 
{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } 
[ 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 statement (DDL Trigger)

ALTER TRIGGER trigger_name 
ON { DATABASE | ALL SERVER } 
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type [ ,...n ] | event_group } 
AS { sql_statement [ ; ] | 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)
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 ]

<method_specifier> ::=
    assembly_name.class_name.method_name

引数

schema_name
DML トリガーが属しているスキーマの名前を指定します。DML トリガーのスコープは、トリガーが作成されたテーブルまたはビューのスキーマです。schema_name は、DML トリガーと、トリガーに対応するテーブルまたはビューが既定のスキーマに属している場合のみ、省略可能です。schema_name は、DDL トリガーまたはログオントリガーに対しては指定できません。
trigger_name
変更する既存のトリガーです。
table | view
DML トリガーが実行されるテーブルまたはビューです。テーブルまたはビューの完全修飾名の指定は省略可能です。
DATABASE
DDL トリガーのスコープを現在のデータベースに適用します。これを指定すると、現在のデータベースで event_type または event_group が発生するたびにトリガーが起動します。
ALL SERVER
DDL トリガーまたはログオントリガーのスコープを現在のサーバーに適用します。これを指定すると、現在のサーバーの任意の場所で event_type または event_group が発生するたびにトリガーが起動します。

WITH ENCRYPTION
ALTER TRIGGER ステートメントのテキストが含まれている sys.syscomments や sys.sql_modules のエントリを暗号化します。WITH ENCRYPTION を使用すると、そのトリガーを SQL Server レプリケーションの一部としてパブリッシュできなくなります。WITH ENCRYPTION は、CLR トリガーには指定できません。

注
トリガーが WITH ENCRYPTION を使用して作成されている場合、それを有効なままにするには、ALTER TRIGGER ステートメントで WITH ENCRYPTION を再度指定する必要があります。

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

詳細については、「EXECUTE AS 句 (Transact-SQL)」を参照してください。
AFTER
トリガーをアクティブにする SQL ステートメントが正常に実行された後にのみ、そのトリガーを起動することを指定します。このトリガーが起動される前に、すべての連鎖参照操作および制約チェックも正常に終了している必要があります。

FOR キーワードのみが指定されている場合は、AFTER が既定値です。

DML AFTER トリガーは、テーブルでのみ定義できます。
INSTEAD OF
その DML トリガーを、トリガーをアクティブにする SQL ステートメントの代わりに実行するように指定します。したがって、トリガーをアクティブにするステートメントの操作は無効になります。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 ] } | { [INSERT ] [ , ] [ UPDATE ] }
このテーブルまたはビューに対して実行したときに DML トリガーをアクティブにするデータ変更ステートメントを指定します。少なくとも 1 つのオプションを指定する必要があります。トリガー定義では、このキーワードを指定する順序や組み合わせを問いません。複数のオプションを指定するときは、オプションをコンマで区切ります。

INSTEAD OF トリガーの場合、目的のテーブルに ON DELETE 連鎖参照操作を指定している参照関係があるときは、DELETE オプションを指定できません。同様に、目的のテーブルに ON UPDATE 連鎖参照操作を指定している参照関係があるときは、UPDATE オプションを指定できません。詳細については、「ALTER TABLE (Transact-SQL)」を参照してください。
event_type
実行後に DDL トリガーが起動される Transact-SQL 言語イベントの名前です。DDL トリガーで使用できるイベントの一覧については、「DDL イベント」を参照してください。
event_group
Transact-SQL 言語イベントの定義済みグループの名前を指定します。DDL トリガーは、event_group に属する Transact-SQL 言語イベントの実行後に起動します。DDL トリガーで使用できるイベントグループの一覧については、「DDL イベントグループ」を参照してください。対応するイベントの種類を sys.trigger_events カタログビューに追加すると、event_group は、ALTER TRIGGER の実行が終了した後、マクロとしても機能します。
NOT FOR REPLICATION
トリガーに関係するテーブルをレプリケーションエージェントが変更するときに、トリガーを実行してはいけないことを示します。詳細については、「NOT FOR REPLICATION を使用した制約、ID、およびトリガの制御」を参照してください。
sql_statement
トリガー条件とトリガー動作です。
<method_specifier>
トリガーにバインドするアセンブリのメソッドを指定します。このメソッドは引数を受け取らず、void を返す必要があります。class_name は、有効な SQL Server 識別子で、アセンブリの表示設定が指定されたアセンブリ内にクラスとして存在する必要があります。クラスを入れ子にはできません。

説明

ALTER TRIGGER の詳細については、「CREATE TRIGGER (Transact-SQL)」の「解説」を参照してください。

DML トリガー

ALTER TRIGGER では、テーブルおよびビューに対する INSTEAD OF トリガーを利用することで、更新可能なビューが手動で使用できます。SQL Server は、ALTER TRIGGER を、他のすべての種類のトリガー (AFTER、INSTEAD-OF) と同様に適用します。

テーブルで実行される最初と最後の AFTER トリガーを、sp_settriggerorder を使用して指定できます。1 つのテーブルにつき、最初と最後の AFTER トリガーはそれぞれ 1 つだけ指定できます。同じテーブルに他の AFTER トリガーが複数ある場合は、ランダムに実行されます。

ALTER TRIGGER ステートメントを使って最初と最後のトリガーを変更し、変更したトリガーに設定されていた最初と最後を示す属性を削除した場合は、sp_settriggerorder を使用して順序の値を再設定する必要があります。

AFTER トリガーは、トリガーを起動する SQL ステートメントが正常に実行された後にのみ実行されます。正常な実行の中には、更新または削除対象のオブジェクトに関連付けられているすべての連鎖参照操作と制約チェックが含まれます。AFTER トリガー操作は、トリガーをアクティブにするステートメントの結果、およびそのステートメントによって発生するすべての UPDATE および DELETE 連鎖参照操作の結果を調べます。

子テーブルまたは参照元テーブルへの DELETE 操作が、親テーブルでの DELETE による CASCADE の結果として発生し、その子テーブルの中で DELETE での INSTEAD OF トリガーが定義されている場合、そのトリガーは無視され、DELETE 操作が実行されます。

DDL トリガー

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

権限

DML トリガーを変更するには、トリガーが定義されているテーブルやビューに対する ALTER 権限が必要です。

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

例

次の例では、ユーザーが SalesPersonQuotaHistory テーブルにデータを追加したり、データを変更したりしようとすると、クライアントに対してユーザー定義のメッセージを出力する DML トリガーを作成します。次に ALTER TRIGGER を使用してトリガーを変更し、トリガーを INSERT 操作だけに適用します。このトリガーは、テーブルの更新や行の挿入を行うユーザーに対して、Compensation 部門にも変更を知らせる必要があることを連絡できるので有用です。

USE AdventureWorks2008R2;
GO
IF OBJECT_ID(N'Sales.bonus_reminder', N'TR') IS NOT NULL
    DROP TRIGGER Sales.bonus_reminder;
GO
CREATE TRIGGER Sales.bonus_reminder
ON Sales.SalesPersonQuotaHistory
WITH ENCRYPTION
AFTER INSERT, UPDATE 
AS RAISERROR ('Notify Compensation', 16, 10);
GO
-- Now, change the trigger.
USE AdventureWorks2008R2;
GO
ALTER TRIGGER Sales.bonus_reminder
ON Sales.SalesPersonQuotaHistory
AFTER INSERT
AS RAISERROR ('Notify Compensation', 16, 10);
GO

次の方法で共有

ALTER TRIGGER (Transact-SQL)

構文

引数

説明

DML トリガー

DDL トリガー

権限

例

関連項目

参照

概念

その他のリソース