ALTER TRIGGER (Transact-SQL)

Artikel
06/18/2013

Diese Anweisung ändert die Definition eines DML-, DDL- oder LOGON-Triggers, der zuvor mit der CREATE TRIGGER-Anweisung erstellt wurde. Trigger werden mit CREATE TRIGGER erstellt. Sie können direkt aus Transact-SQL-Anweisungen oder aus Methoden von Assemblys erstellt werden, die in der Microsoft .NET Framework Common Language Runtime (CLR) erstellt werden und durch Hochladen an eine Instanz von SQL Server übertragen werden. Weitere Informationen zu den in der ALTER TRIGGER-Anweisung verwendeten Parametern finden Sie unter CREATE TRIGGER (Transact-SQL).

Themenlink (Symbol) Transact-SQL-Syntaxkonventionen

Syntax

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

Argumente

schema_name
Der Name des Schemas, zu dem ein DML-Trigger gehört. DML-Trigger werden auf das Schema der Tabelle oder der Sicht begrenzt, in denen sie erstellt werden. schema_name ist nur optional, wenn der DML-Trigger und die zugehörige Tabelle oder Sicht zum Standardschema gehören. schema_name kann für DDL- oder LOGON-Trigger nicht angegeben werden.
trigger_name
Der vorhandene Trigger, der geändert werden soll.
table | view
Die Tabelle oder Sicht, für die der DML-Trigger ausgeführt wird. Das Angeben des vollqualifizierten Namens einer Tabelle oder Sicht ist optional.
DATABASE
Wendet den Bereich eines DDL-Triggers auf die aktuelle Datenbank an. Wenn angegeben, wird der Trigger jedes Mal ausgelöst, wenn in der aktuellen Datenbank event_type oder event_group auftritt.
ALL SERVER
Wendet den Bereich eines DDL- oder LOGON-Triggers auf den aktuellen Server an. Wenn angegeben, wird der Trigger jedes Mal ausgelöst, wenn auf dem aktuellen Server event_type oder event_group auftritt.
WITH ENCRYPTION
Verschlüsselt die sys.syscomments sys.sql_modules-Einträge, die den Text der ALTER TRIGGER-Anweisung enthalten. Durch das Verwenden von WITH ENCRYPTION kann verhindert werden, dass der Trigger als Teil der SQL Server-Replikation veröffentlicht wird. WITH ENCRYPTION kann nicht für CLR-Trigger angegeben werden.

Hinweis

Wenn ein Trigger mit WITH ENCRYPTION erstellt wird, muss er erneut in der ALTER TRIGGER-Anweisung angegeben werden, damit diese Option aktiviert bleibt.
EXECUTE AS
Gibt den Sicherheitskontext an, unter dem der Trigger ausgeführt wird. Ermöglicht die Steuerung des Benutzerkontos, das die Instanz von SQL Server verwendet, um die Berechtigungen für alle Datenbankobjekte zu überprüfen, auf die durch den Trigger verwiesen wird.

Weitere Informationen finden Sie unter EXECUTE AS-Klausel (Transact-SQL).
AFTER
Gibt an, dass der Trigger erst ausgelöst wird, nachdem die auslösende SQL-Anweisung erfolgreich ausgeführt wurde. Vor dem Auslösen dieses Triggers müssen außerdem alle für die referenzielle Integrität erforderlichen kaskadierenden Aktionen und Einschränkungsüberprüfungen erfolgreich abgeschlossen sein.

AFTER ist die Standardeinstellung, wenn nur das FOR-Schlüsselwort angegeben ist.

DML AFTER-Trigger können nur für Tabellen definiert werden.
INSTEAD OF
Gibt an, dass der DML-Trigger anstelle der auslösenden SQL-Anweisung ausgeführt wird, wodurch die Aktionen der auslösenden Anweisungen überschrieben werden. INSTEAD OF kann für DDL- oder LOGON-Trigger nicht angegeben werden.

Es kann nur maximal ein INSTEAD OF-Trigger pro INSERT-, UPDATE- oder DELETE-Anweisung für eine Tabelle oder Sicht definiert werden. Es ist jedoch möglich, Sichten auf Sichten zu definieren, wobei jede Sicht über einen eigenen INSTEAD OF-Trigger verfügt.

INSTEAD OF-Trigger sind in Sichten die mit WITH CHECK OPTION erstellt wurden, nicht zulässig. SQL Server löst einen Fehler aus, wenn ein INSTEAD OF-Trigger einer Sicht hinzufügt wird, für die die WITH CHECK OPTION angegeben wurde. Der Benutzer muss die Option mithilfe von ALTER VIEW entfernen, bevor der INSTEAD OF-Trigger definiert wird.
{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } | { [INSERT ] [ , ] [ UPDATE ] }
Gibt die Datenänderungsanweisungen an, die den DML-Trigger aktivieren, wenn sie auf diese Tabelle oder Sicht angewendet werden. Es muss mindestens eine Option angegeben werden. Die Optionen können in beliebiger Kombination und Reihenfolge in der Triggerdefinition angegeben werden. Wenn Sie mehrere Optionen angeben, trennen Sie diese durch Kommas.

Für INSTEAD OF-Trigger ist die Option DELETE nicht für Tabellen mit einer referenziellen Beziehung untereinander zulässig, wenn für ON DELETE die Option CASCADE angegeben ist. Ebenso ist die Option UPDATE nicht für Tabellen mit einer referenziellen Beziehung untereinander zulässig, wenn für ON UPDATE die Option CASCADE angegeben ist. Weitere Informationen finden Sie unter ALTER TABLE (Transact-SQL).
event_type
Der Name eines Transact-SQL-Sprachereignisses, nach dessen Ausführung ein DDL-Trigger ausgelöst wird. Gültige Ereignisse für DDL-Trigger werden unter DDL-Ereignisse aufgeführt.
event_group
Der Name einer vordefinierten Gruppe von Transact-SQL-Sprachereignissen. Der DDL-Trigger wird nach dem Ausführen eines beliebigen Transact-SQL-Ereignisses ausgelöst, das zu event_group gehört. Gültige Ereignisgruppen für DDL-Trigger werden unter DDL-Ereignisgruppen aufgeführt. Nach dem Ausführen von ALTER TRIGGER dient event_group außerdem als Makro, indem der sys.trigger_events-Katalogsicht mit diesem Parameter die betroffenen Ereignistypen hinzugefügt werden.
NOT FOR REPLICATION
Zeigt an, dass der Trigger nicht ausgeführt werden soll, wenn ein Replikations-Agent die vom Trigger betroffene Tabelle ändert.
sql_statement
Die Triggerbedingungen und Aktionen.
<method_specifier>
Gibt die Methode an, mit der eine Assembly eine Bindung mit dem Trigger herstellt. Die Methode darf keine Argumente enthalten und muss "void" zurückgeben. class_name muss ein gültiger SQL Server-Bezeichner sein und als Klasse mit Assemblysichtbarkeit in der Assembly vorhanden sein. Bei der Klasse darf es sich nicht um eine geschachtelte Klasse handeln.

Hinweis
Wenn ein Trigger mit WITH ENCRYPTION erstellt wird, muss er erneut in der ALTER TRIGGER-Anweisung angegeben werden, damit diese Option aktiviert bleibt.

Hinweise

Weitere Informationen zu ALTER TRIGGER finden Sie in den Hinweisen unter CREATE TRIGGER (Transact-SQL).

Hinweis
Die EXTERNAL_NAME- und ON_ALL_SERVER-Optionen sind in einer enthaltenen Datenbank nicht verfügbar.

DML-Trigger

ALTER TRIGGER unterstützt manuell aktualisierbare Sichten durch INSTEAD OF-Trigger in Tabellen und Sichten. SQL Server wendet ALTER TRIGGER analog zu allen anderen Triggern (AFTER, INSTEAD-OF) an.

Der erste und der letzte AFTER-Trigger, die für eine Tabelle ausgeführt werden sollen, können mithilfe von sp_settriggerorder angegeben werden. Nur der erste und ein letzter AFTER-Trigger können für eine Tabelle angegeben werden. Sind für eine Tabelle weitere AFTER-Trigger vorhanden, werden diese nach dem Zufallsprinzip ausgeführt.

Wenn eine ALTER TRIGGER-Anweisung den ersten oder den letzten Trigger ändert, wird das erste oder das letzte für den geänderten Trigger festgelegte Attribut gelöscht, und der Reihenfolgewert muss mithilfe von sp_settriggerorder neu festgelegt werden.

Ein AFTER-Trigger wird nur dann ausgeführt, wenn die den Trigger auslösende SQL-Anweisung erfolgreich ausgeführt wurde. Diese erfolgreiche Ausführung umfasst alle referenziellen kaskadierenden Aktionen und Einschränkungsüberprüfungen, die mit dem aktualisierten oder gelöschten Objekt verknüpft sind. Bei dem AFTER-Triggervorgang werden die Auswirkungen der auslösenden Anweisung sowie alle referenziellen kaskadierenden UPDATE- und DELETE-Aktionen geprüft, die durch die auslösende Anweisung verursacht werden.

Wenn eine DELETE-Aktion für eine untergeordnete oder verweisende Tabelle das Ergebnis einer CASCADE-Aktion für eine DELETE-Aktion aus der übergeordneten Tabelle ist und wenn für DELETE ein INSTEAD OF-Trigger für diese untergeordnete Tabelle definiert ist, wird der Trigger ignoriert und die DELETE-Aktion ausgeführt.

DDL-Trigger

Im Gegensatz zu DML-Triggern haben DDL-Trigger keinen Schemabereich. Deshalb können OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY und OBJECTPROPERTY(EX) nicht verwendet werden, wenn Metadaten zu DDL-Triggern abgefragt werden. Verwenden Sie stattdessen die Katalogsichten. Weitere Informationen finden Sie unter Abrufen von Informationen zu DDL-Triggern.

Berechtigungen

Zum Ändern eines DML-Triggers ist eine ALTER-Berechtigung für die Tabelle oder Sicht erforderlich, für die der Trigger definiert ist.

Zum Ändern eines DDL-Triggers, der mit einem Serverbereich (ON ALL SERVER) definiert ist, oder eines LOGON-Triggers ist die CONTROL SERVER-Berechtigung auf dem Server erforderlich. Zum Ändern eines DDL-Triggers, der mit einem Datenbankbereich (ON DATABASE ANY) definiert ist, ist die ALTER ANY DATABASE DDL TRIGGER-Berechtigung in der aktuellen Datenbank erforderlich.

Beispiele

Im folgenden Beispiel wird ein DML-Trigger erstellt, der eine benutzerdefinierte Meldung an den Client ausgibt, wenn ein Benutzer versucht, Daten in der SalesPersonQuotaHistory-Tabelle hinzuzufügen oder zu ändern. Der Trigger wird dann mit ALTER TRIGGER geändert, damit der Trigger nur für INSERT-Aktivitäten angewendet wird. Dieser Trigger ist hilfreich, da er den Benutzer beim Aktualisieren oder Einfügen von Zeilen in die Tabelle daran erinnert, dass auch die Abteilung Compensation benachrichtigt werden muss.

USE AdventureWorks2012;
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 AdventureWorks2012;
GO
ALTER TRIGGER Sales.bonus_reminder
ON Sales.SalesPersonQuotaHistory
AFTER INSERT
AS RAISERROR ('Notify Compensation', 16, 10);
GO