CREATE TRIGGER (Transact-SQL)

Aktualisiert: 17. Juli 2006

Erstellt einen DML-, DDL- oder LOGON-Trigger. Ein Trigger ist eine spezielle Art von gespeicherter Prozedur, die automatisch ausgeführt wird, wenn ein Ereignis auf dem Datenbankserver auftritt. DML-Trigger werden ausgeführt, wenn ein Benutzer versucht, Daten mithilfe eines DML-Ereignisses (Data Manipulation Language, Datenbearbeitungssprache) zu ändern. DML-Ereignisse sind INSERT-, UPDATE- oder DELETE-Anweisungen für eine Tabelle oder Sicht.

Hinweis:
Diese Trigger werden ausgelöst, sobald ein beliebiges gültiges Ereignis ausgelöst wird, unabhängig davon, ob Tabellenzeilen betroffen sind oder nicht. Dies ist programmbedingt.

DDL-Trigger werden als Reaktion auf verschiedene DDL-Ereignisse (Data Definition Language – Datendefinitionssprache) ausgeführt. Diese Ereignisse entsprechen im Wesentlichen den Anweisungen CREATE, ALTER und DROP von Transact-SQL sowie bestimmten gespeicherten Systemprozeduren, die DDL-ähnliche Vorgänge ausführen. LOGON-Trigger werden als Reaktion auf das LOGON-Ereignis ausgelöst, das wiederum ausgelöst wird, wenn eine Benutzersitzung eingerichtet wird. Trigger können in SQL Server 2005-Datenbankmodul direkt mit Transact-SQL-Anweisungen oder mit Methoden von Assemblys erstellt werden, die in der Microsoft .NET Framework Common Language Runtime (CLR) erstellt und auf eine Instanz von SQL Server hochgeladen werden können. SQL Server ermöglicht das Erstellen mehrerer Trigger für jede spezifische Anweisung.

Sicherheitshinweis:
Bösartiger Code innerhalb von Triggern kann unter ausgeweiteten Privilegien ausgeführt werden. Weitere Informationen zur Abwehr dieses Sicherheitsrisikos finden Sie unter Verwalten der Triggersicherheit.

Transact-SQL-Syntaxkonventionen

Syntax

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

Argumente

• schema_name
  Der Name des Schemas, zu dem ein DML-Trigger gehört. Der Bereich von DML-Triggern bezieht sich auf das Schema der Tabelle oder Sicht, in der sie erstellt werden. schema_name kann nicht für DDL-Trigger oder LOGON-Trigger angegeben werden.

• trigger_name
  Der Name des Triggers. Werte für trigger_name müssen den Regeln für Bezeichner entsprechen, mit der Ausnahme, dass trigger_name nicht mit # oder ## beginnen darf.

• table | view
  Die Tabelle oder Sicht, für die der DML-Trigger ausgeführt wird; zuweilen wird diese als Triggertabelle oder Triggersicht bezeichnet. Die Angabe des vollqualifizierten Namens der Tabelle oder Sicht ist optional. Auf eine Sicht kann nur von einem INSTEAD OF-Trigger verwiesen werden. DML-Trigger können nicht für lokale oder globale temporäre Tabellen definiert werden.

• 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
  Verbirgt den Text der CREATE TRIGGER-Anweisung. Durch die Verwendung von WITH ENCRYPTION kann verhindert werden, dass der Trigger im Rahmen der SQL Server-Replikation veröffentlicht wird. WITH ENCRYPTION kann für CLR-Trigger nicht angegeben werden.

• EXECUTE AS
  Gibt den Sicherheitskontext an, unter dem der Trigger ausgeführt wird. Sie können steuern, welches Benutzerkonto die Instanz von SQL Server verwendet, um so die Berechtigungen für beliebige Datenbankobjekte zu überprüfen, auf die der Trigger verweist.

  Weitere Informationen finden Sie unter EXECUTE AS-Klausel (Transact-SQL).

• FOR | AFTER
  AFTER gibt an, dass der Trigger nur dann ausgelöst wird, wenn alle Vorgänge, die in der den Trigger auslösenden SQL-Anweisung festgelegt sind, erfolgreich ausgeführt wurden. Alle referenziellen CASCADE-Aktionen und Einschränkungsüberprüfungen müssen ebenfalls erfolgreich ausgeführt worden sein, bevor dieser Trigger ausgelöst wird.

  AFTER ist die Standardeinstellung, wenn FOR das einzige angegebene Schlüsselwort ist.

  AFTER-Trigger können für Sichten nicht 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 außer Kraft gesetzt 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. Sie können jedoch Sichten für Sichten definieren, wobei jede Sicht über einen eigenen INSTEAD OF-Trigger verfügt.

  INSTEAD OF-Trigger sind in aktualisierbaren Sichten, die WITH CHECK OPTION verwenden, nicht zulässig. SQL Server löst einen Fehler aus, falls ein INSTEAD OF-Trigger zu einer aktualisierbaren Sicht hinzugefügt wird, die mit WITH CHECK OPTION erstellt wurde. Der Benutzer muss die Option mithilfe von ALTER VIEW entfernen, bevor der INSTEAD OF-Trigger definiert werden kann.

• { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
  Gibt die Anweisungen zur Datenänderung an, die den DML-Trigger aktivieren, wenn Sie ihn für diese Tabelle oder Sicht auszuführen versuchen. Es muss mindestens eine Option angegeben werden. Die Optionen können in beliebiger Kombination und Reihenfolge in der Triggerdefinition angegeben werden.

  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.

• event_type
  Der Name eines Transact-SQL-Sprachereignisses, dessen Ausführung bewirkt, dass ein DDL-Trigger ausgelöst wird. Ereignisse, die in DDL-Triggern verwendet werden können, werden unter DDL-Ereignisse für die Verwendung mit DDL-Triggern 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. Ereignisgruppen, die in DDL-Triggern verwendet werden können, werden unter Ereignisgruppen für die Verwendung mit DLL-Triggern aufgeführt.

  Nach dem Ausführen von CREATE TRIGGER fungiert event_group außerdem als Makro, indem dieser Parameter die betroffenen Ereignistypen der sys.trigger_events-Katalogsicht hinzufügt.

• WITH APPEND
  Gibt an, dass ein weiterer Trigger eines vorhandenen Typs hinzugefügt werden soll. Diese optionale Klausel wird nur benötigt, wenn der Kompatibilitätsgrad kleiner oder gleich 65 ist. Falls der Kompatibilitätsgrad größer oder gleich 70 ist, ist die WITH APPEND-Klausel nicht erforderlich, um einen zusätzlichen Trigger eines vorhandenen Typs hinzuzufügen. Dies ist das Standardverhalten von CREATE TRIGGER bei einer Kompatibilitätsebeneneinstellung von größer oder gleich 70. Weitere Informationen finden Sie unter sp_dbcmptlevel (Transact-SQL).

  WITH APPEND kann nicht mit INSTEAD OF-Triggern verwendet werden oder falls der AFTER-Trigger explizit angegeben ist. Aus Gründen der Abwärtskompatibilität kann WITH APPEND nur verwendet werden, wenn FOR angegeben ist, INSTEAD OF oder AFTER jedoch nicht. WITH APPEND kann nicht angegeben werden, wenn EXTERNAL NAME angegeben ist (wenn es sich bei dem Trigger also um einen CLR-Trigger handelt).

  Wichtig:
  WITH APPEND wird in einer zukünftigen Version von Microsoft SQL Server nicht mehr unterstützt. Vermeiden Sie die Verwendung von WITH APPEND bei neuen Entwicklungsarbeiten, und planen Sie die Änderung von Anwendungen, in denen WITH APPEND verwendet wird.

• NOT FOR REPLICATION
  Zeigt an, dass der Trigger nicht ausgeführt werden soll, wenn ein Replikations-Agent die vom Trigger betroffene Tabelle ändert. Weitere Informationen finden Sie unter Steuern von Einschränkungen, Identitäten und Triggern mithilfe von NOT FOR REPLICATION.

• sql_statement
  Die Triggerbedingungen und -aktionen. Triggerbedingungen geben zusätzliche Kriterien an, die bestimmen, ob der Versuch, DML-, DDL- oder LOGON-Ereignisse auszulösen, die Triggeraktionen auslöst.

  Die in den Transact-SQL-Anweisungen angegebenen Triggeraktionen treten in Kraft, wenn versucht wird, den Vorgang auszuführen.

  Trigger können beliebig viele Transact-SQL-Anweisungen jeglicher Art enthalten, einschließlich Ausnahmen. Weitere Informationen finden Sie in den Hinweisen. Ein Trigger ist dafür konzipiert, Daten auf der Grundlage einer Datenänderungs- oder Definitionsanweisung zu prüfen oder zu ändern, jedoch nicht dafür, Daten an den Benutzer zurückzugeben. Die Transact-SQL-Anweisungen in einem Trigger enthalten häufig Sprachkonstrukte zur Ablaufsteuerung.

  DML-Trigger verwenden die logischen (konzeptionellen) Tabellen deleted und inserted. Strukturell ähneln sie der Tabelle, für die der Trigger definiert wurde, d. h. der Tabelle, für die versucht wurde, die Benutzeraktion auszuführen. Die deleted- und die inserted-Tabelle enthalten die alten oder die neuen Werte der Zeilen, die möglicherweise durch die Benutzeraktion geändert werden. Um beispielsweise alle Werte in der deleted-Tabelle abzurufen, verwenden Sie:

  SELECT *
  FROM deleted
  

  Weitere Informationen finden Sie unter Verwenden der Tabellen inserted und deleted.

  DDL- und LOGON-Trigger zeichnen Informationen zu dem auslösenden Ereignis auf, indem sie die EVENTDATA (Transact-SQL)-Funktion verwenden. Weitere Informationen finden Sie unter Verwenden der EVENTDATA-Funktion.

  In einem DELETE-, INSERT- oder UPDATE-Trigger werden von SQL Server keine text-, ntext- oder image-Spaltenreferenzen in eingefügten und gelöschten Tabellen zugelassen, wenn der Kompatibilitätsgrad auf 70 festgelegt wurde. Auf die text-, ntext- und ntext-Werte in den eingefügten und gelöschten Tabellen kann nicht zugegriffen werden. Um den neuen Wert in einem INSERT- oder UPDATE-Trigger abzurufen, verknüpfen Sie die inserted-Tabelle mit der ursprünglichen Aktualisierungstabelle. Wenn der Kompatibilitätsgrad kleiner oder gleich 65 ist, werden für die text-, ntext- oder image-Spalten der inserted- oder der deleted-Tabelle, die NULL-Werte zulassen, NULL-Werte zurückgegeben. Leere Zeichenfolgen werden zurückgegeben, wenn die Spalten keine NULL-Werte zulassen.

  Falls der Kompatibilitätsgrad größer oder gleich 80 ist, lässt SQL Server die Aktualisierung der text-, ntext- oder image-Spalten durch den INSTEAD OF-Trigger für Tabellen oder Sichten zu.

  Wichtig:

  Die Datentypen ntext, text und image werden in einer zukünftigen Version von Microsoft SQL Server entfernt. Vermeiden Sie die Verwendung dieser Datentypen bei neuen Entwicklungen, und planen Sie die Änderung von Anwendungen, in denen sie aktuell verwendet werden. Verwenden Sie stattdessen nvarchar(max), varchar(max) und varbinary(max). Sowohl AFTER- als auch INSTEAD OF-Trigger unterstützen varchar(MAX)-, nvarchar(MAX)- und varbinary(MAX)-Daten in der inserted- und der deleted-Tabelle.

• < method_specifier >
  Gibt für einen CLR-Trigger die Methode einer Assembly an, die an den Trigger gebunden werden soll. Die Methode darf keine Argumente annehmen und muss 'void' zurückgeben. class_name muss ein gültiger SQL Server-Bezeichner sein und als Klasse mit Assembly-Sichtbarkeit in der Assembly vorhanden sein. Wenn die Klasse über einen mit einem Namespace qualifizierten Namen verfügt, der "." verwendet, um die einzelnen Bestandteile des Namespace voneinander zu trennen, muss im Klassennamen [ ] oder " " als Trennzeichen verwendet werden. Bei der Klasse darf es sich nicht um eine geschachtelte Klasse handeln.

  Hinweis:
  Standardmäßig ist die Option zum Ausführen von CLR-Code für SQL Server deaktiviert. Sie können Datenbankobjekte, die auf verwaltete Codemodule verweisen, erstellen, ändern und löschen, aber diese Verweise werden in einer Instanz von SQL Server nicht ausgeführt, es sei denn, die Option clr enabled wurde mithilfe von sp_configure aktiviert.

Hinweise

DML-Trigger

DML-Trigger werden häufig verwendet, um Geschäftsregeln und Datenintegrität zu erzwingen. SQL Server bietet deklarative referenzielle Integrität (DRI) durch die ALTER TABLE- und CREATE TABLE-Anweisungen. DRI stellt jedoch keine datenbankübergreifende referenzielle Integrität sicher. Referenzielle Integrität bezieht sich auf die Regeln über die Beziehungen zwischen den Primär- und den Fremdschlüsseln von Tabellen. Um referenzielle Integrität zu erzwingen, verwenden Sie die PRIMARY KEY- und FOREIGN KEY-Einschränkungen in ALTER TABLE und CREATE TABLE. Wenn es für die Triggertabelle Einschränkungen gibt, werden diese geprüft, nachdem der INSTEAD OF-Trigger ausgeführt wurde und bevor der AFTER-Trigger ausgeführt wird. Falls eine Verletzung der Einschränkungen vorliegt, wird für die Aktionen des INSTEAD OF-Triggers ein Rollback ausgeführt. Der AFTER-Trigger wird nicht ausgelöst.

Der erste und der letzte AFTER-Trigger, die für eine Tabelle ausgeführt werden sollen, können mithilfe von sp_settriggerorder angegeben werden. In einer Tabelle kann für jeden INSERT-, UPDATE- und DELETE-Vorgang nur ein erster und ein letzter AFTER-Trigger angegeben werden. Sind weitere AFTER-Trigger für die Tabelle 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. Die erfolgreiche Ausführung umfasst alle referenziellen kaskadierenden Aktionen und Einschränkungsüberprüfungen, die mit dem aktualisierten oder gelöschten Objekt verbunden sind.

Falls ein für eine Tabelle definierter INSTEAD OF-Trigger eine Anweisung für die Tabelle ausführt, die normalerweise den INSTEAD OF-Trigger erneut auslösen würde, wird der Trigger nicht erneut aufgerufen. Stattdessen wird die Anweisung so verarbeitet, als ob in der Tabelle kein INSTEAD OF-Trigger vorhanden wäre, und die Kette der Einschränkungsvorgänge und AFTER-Triggerausführungen wird gestartet. Wenn z. B. ein Trigger als INSTEAD OF INSERT-Trigger für eine Tabelle definiert ist und der Trigger eine INSERT-Anweisung in derselben Tabelle ausführt, ruft die durch den INSTEAD OF-Trigger ausgeführte INSERT-Anweisung den Trigger nicht erneut auf. Die INSERT-Anweisung, die durch den Trigger ausgeführt wird, startet das Ausführen der Einschränkungsaktionen und löst alle für die Tabelle definierten AFTER INSERT-Trigger aus.

Falls ein für eine Sicht definierter INSTEAD OF-Trigger eine Anweisung für die Sicht ausführt, die normalerweise den INSTEAD OF-Trigger erneut auslösen würde, wird der Trigger nicht erneut aufgerufen. Stattdessen wird die Anweisung als Änderungen an den zugrunde liegenden Basistabellen der Sicht aufgelöst. In diesem Fall muss die Sichtdefinition alle Einschränkungen für eine aktualisierbare Sicht erfüllen. Eine Definition zu aktualisierbaren Sichten finden Sie unter Ändern von Daten über eine Sicht.

Wenn z. B. ein Trigger als INSTEAD OF UPDATE-Trigger für eine Sicht definiert ist und der Trigger eine UPDATE-Anweisung in derselben Sicht ausführt, ruft die durch den INSTEAD OF-Trigger ausgeführte UPDATE-Anweisung den Trigger nicht erneut auf. Die von dem Trigger ausgeführte UPDATE-Anweisung wird für die Sicht so verarbeitet, als ob in der Sicht kein INSTEAD OF-Trigger vorhanden wäre. Die von der UPDATE-Anweisung geänderten Spalten müssen in eine einzige Basistabelle aufgelöst werden. Jede Änderung an einer zugrunde liegenden Basistabelle startet die Kette der definierten Einschränkungen und löst die für die Tabelle definierten AFTER-Trigger aus.

Testen auf UPDATE- oder INSERT-Aktionen in angegebenen Spalten

Sie können einen Transact-SQL-Trigger erstellen, der auf der Grundlage von UPDATE- oder INSERT-Änderungen an den angegebenen Spalten bestimmte Aktionen ausführt. Verwenden Sie hierzu UPDATE() oder COLUMNS_UPDATED innerhalb des Triggertexts. UPDATE() testet auf UPDATE- oder INSERT-Versuche in einer Spalte. COLUMNS_UPDATED testet auf UPDATE- oder INSERT-Aktionen, die für mehrere Spalten durchgeführt wurden, und gibt ein Bitmuster zurück, dem Sie entnehmen können, welche Spalten eingefügt oder aktualisiert wurden.

Beschränkungen bei der Verwendung von Triggern

CREATE TRIGGER muss die erste Anweisung in einem Batch sein und kann sich nur auf eine Tabelle beziehen.

Ein Trigger kann nur in der aktuellen Datenbank erstellt werden; er darf jedoch auf Objekte außerhalb der aktuellen Datenbank verweisen.

Wenn der Name des Triggerschemas angegeben ist, um den Trigger zu kennzeichnen, kennzeichnen Sie den Tabellennamen auf die gleiche Weise.

Es ist möglich, in derselben CREATE TRIGGER-Anweisung dieselbe Triggeraktion für mehrere Benutzeraktionen festzulegen (beispielsweise INSERT und UPDATE).

INSTEAD OF DELETE/UPDATE-Trigger können nicht für eine Tabelle definiert werden, die einen Fremdschlüssel hat, für den ON DELETE/UPDATE CASCADE angegeben ist.

In einem Trigger kann jede beliebige SET-Anweisung angegeben werden. Die ausgewählte SET-Option bleibt während der Ausführung des Triggers in Kraft und kehrt dann zur vorherigen Einstellung zurück.

Wenn ein Trigger ausgelöst wird, werden die Ergebnisse wie bei einer gespeicherten Prozedur an die aufrufende Anwendung zurückgegeben. Um zu verhindern, dass Ergebnisse aufgrund einer Triggerauslösung an eine Anwendung zurückgegeben werden, verwenden Sie in einem Trigger keine SELECT-Anweisungen, die Ergebnisse zurückgeben, oder Anweisungen, die Variablenzuweisungen durchführen. Ein Trigger, der SELECT-Anweisungen enthält, die Ergebnisse an den Benutzer zurückgeben, oder Anweisungen, die Variablenzuweisungen durchführen, erfordert eine besondere Bearbeitung. Diese zurückgegebenen Ergebnisse müssten sonst in allen Anwendungen geschrieben werden, in denen Änderungen an der Triggertabelle zulässig sind. Wenn Variablenzuweisungen in einem Trigger erfolgen müssen, verwenden Sie eine SET NOCOUNT-Anweisung am Anfang des Triggers, um die Rückgabe von Resultsets zu verhindern.

Obwohl eine TRUNCATE TABLE-Anweisung tatsächlich eine DELETE-Anweisung ist, kann diese Anweisung keinen Trigger aktivieren, da der Vorgang keine einzelnen Zeilenlöschungen protokolliert. Es ist jedoch nur für Benutzer mit Berechtigungen für eine Tabelle zur Ausführung einer TRUNCATE TABLE-Anweisung relevant, dass durch eine TRUNCATE TABLE-Anweisung ein DELETE-Trigger unbeabsichtigt umgangen werden kann.

Die WRITETEXT-Anweisung, ob protokolliert oder nicht protokolliert, aktiviert keinen Trigger.

Die folgenden Transact-SQL-Anweisungen sind in einem DML-Trigger nicht zulässig:

ALTER DATABASE	CREATE DATABASE	DROP DATABASE
LOAD DATABASE	LOAD LOG	RECONFIGURE
RESTORE DATABASE	RESTORE LOG

Darüber hinaus sind die folgenden Transact-SQL-Anweisungen im Text des DML-Triggers nicht zulässig, wenn dieser für eine Tabelle oder Sicht verwendet wird, die das Ziel der den Trigger auslösenden Aktion ist.

Wichtig:
Diese Einschränkung wird in SQL Server 2005 eingeführt, sie wird jedoch ebenfalls erzwungen, wenn der Abwärtskompatibilitätsmodus auf 80 festgelegt wird.

CREATE INDEX	ALTER INDEX	DROP INDEX
DBCC DBREINDEX	ALTER PARTITION FUNCTION	DROP TABLE
ALTER TABLE bei Verwendung für die folgenden Aufgaben: Hinzufügen, Ändern oder Löschen von Spalten Wechseln zwischen Partitionen Hinzufügen oder Löschen von PRIMARY KEY- oder UNIQUE-Einschränkungen

Hinweis:
Da SQL Server benutzerdefinierte Trigger für Systemtabellen nicht unterstützt, sollten Sie für Systemtabellen keine benutzerdefinierten Trigger erstellen.

DDL-Trigger

DDL-Trigger führen, ebenso wie Standardtrigger, gespeicherte Prozeduren als Reaktion auf ein Ereignis aus. Im Gegensatz zu Standardtriggern führen sie diese jedoch nicht als Reaktion auf UPDATE-, INSERT- oder DELETE-Anweisungen für eine Tabelle oder Sicht aus, sondern als Reaktion auf DDL-Anweisungen (Data Definition Language, Datendefinitionssprache). Hierzu gehören CREATE-, ALTER-, DROP-, GRANT-, DENY-, REVOKE- und UPDATE STATISTICS-Anweisungen. Bestimmte gespeicherte Systemprozeduren, die DDL-ähnliche Vorgänge ausführen, können ebenfalls DDL-Trigger auslösen.

Wichtig:
Testen Sie die verwendeten DDL-Trigger, um ihre Antworten auf die Ausführung von gespeicherten Systemprozeduren zu ermitteln. Beispielsweise wird durch die CREATE TYPE-Anweisung ebenso wie durch die gespeicherte Prozedur sp_addtype ein DDL-Trigger ausgelöst, der für ein CREATE_TYPE-Ereignis erstellt wurde. Die gespeicherte Prozedur sp_rename löst dagegen keine DDL-Trigger aus.

Weitere Informationen zu DDL-Triggern finden Sie unter DDL-Trigger.

DDL-Trigger werden nicht als Antwort auf Ereignisse ausgelöst, die sich auf lokale oder globale temporäre Tabellen und gespeicherte Prozeduren auswirken.

Im Gegensatz zu DML-Triggern werden für DDL-Trigger keine Schemas als Bereiche festgelegt. Aus diesem Grund können OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY und OBJECTPROPERTYEX nicht verwendet werden, um Metadaten aus DDL-Triggern abzufragen. Verwenden Sie stattdessen Katalogsichten. Weitere Informationen finden Sie unter Abrufen von Informationen zu DLL-Triggern.

Hinweis:
DDL-Trigger mit Serverbereich werden im Ordner Trigger des Objekt-Explorers von SQL Server Management Studio angezeigt. Dieser Ordner befindet sich unter dem Ordner Serverobjekte. DDL-Trigger mit Datenbankbereich werden im Ordner Datenbanktrigger angezeigt. Dieser Ordner befindet sich unter dem Ordner Programmierbarkeit der entsprechenden Datenbank.

Logon-Trigger

LOGON-Trigger werden als Reaktion auf das LOGON-Ereignis ausgelöst. Dieses Ereignis wird ausgelöst, wenn eine Benutzersitzung eingerichtet wird. Weitere Informationen finden Sie unter Logon-Trigger.

Allgemeine Überlegungen zu Triggern

Zurückgeben von Ergebnissen

Die Möglichkeit, Ergebnisse aus Triggern zurückzugeben, wird in einer künftigen Version von SQL Server entfernt. Durch Trigger, die Resultsets zurückgeben, kann es in Anwendungen, die hierfür nicht konzipiert wurden, zu unerwartetem Verhalten kommen. Vermeiden Sie sie deshalb bei Neuentwicklungen, Resultsets aus Triggern zurückzugeben, und planen Sie die Änderung von Anwendungen, in denen dies derzeit verwendet wird. Legen Sie die Option disallow results from triggers auf 1 fest, um zu verhindern, dass Trigger in SQL Server 2005 Resultsets zurückgeben.

LOGON-Trigger verhindern stets, dass Resultsets zurückgegeben werden. Dies ist ein nicht konfigurierbares Verhalten. Wenn von einem LOGON-Trigger ein Resultset generiert wird, kann der Trigger nicht ausgeführt werden, und der Versuch der Anmeldung, der den Trigger ausgelöst hat, wird verweigert.

Mehrere Trigger

SQL Server ermöglicht das Erstellen mehrerer Trigger für jedes DML-, DDL- oder LOGON-Ereignis. Wenn zum Beispiel CREATE TRIGGER FOR UPDATE für eine Tabelle ausgeführt wird, die bereits über einen UPDATE-Trigger verfügt, wird ein zusätzlicher UPDATE-Trigger erstellt. In früheren Versionen von SQL Server war pro Tabelle nur ein Trigger für jedes INSERT-, UPDATE- oder DELETE-Datenänderungsereignis zulässig.

Hinweis:

Bei einem Kompatibilitätsgrad von 70 besteht das Standardverhalten für CREATE TRIGGER darin, zusätzliche Trigger zu vorhandenen Triggern hinzuzufügen, wenn sich die Triggernamen unterscheiden. Bei gleichen Triggernamen gibt SQL Server eine Fehlermeldung zurück. Ist der Kompatibilitätsgrad jedoch kleiner oder gleich 65, ersetzen neue Trigger, die mithilfe der CREATE TRIGGER-Anweisung erstellt wurden, vorhandene Trigger desselben Typs. Dies geschieht selbst dann, wenn die Triggernamen unterschiedlich sind. Weitere Informationen finden Sie unter sp_dbcmptlevel (Transact-SQL).

Rekursive Trigger

SQL Server ermöglicht außerdem den rekursiven Aufruf von Triggern, wenn die RECURSIVE_TRIGGERS-Einstellung mithilfe von ALTER DATABASE aktiviert wurde.

Rekursive Trigger ermöglichen die folgenden Arten von Rekursion:

• Indirekte Rekursion
  Bei der indirekten Rekursion aktualisiert eine Anwendung die T1-Tabelle. Dadurch wird der TR1-Trigger ausgelöst, der die T2-Tabelle aktualisiert. In diesem Szenario wird anschließend der TR2-Trigger ausgelöst, der die T1-Tabelle aktualisiert.
• Direkte Rekursion
  Bei der direkten Rekursion aktualisiert die Anwendung die T1-Tabelle. Dadurch wird der TR1-Trigger ausgelöst, der die T1-Tabelle aktualisiert. Da die T1-Tabelle aktualisiert wurde, wird der TR1-Trigger erneut ausgelöst usw.

Im folgenden Beispiel werden sowohl die indirekte als auch die direkte Triggerrekursion verwendet. Angenommen, für die T1-Tabelle wurden zwei Aktualisierungstrigger, TR1 und TR2, definiert. Der TR1-Trigger aktualisiert die T1-Tabelle rekursiv. Eine UPDATE-Anweisung führt TR1 und TR2 je einmal aus. Darüber hinaus löst die Ausführung von TR1 die Ausführung von TR1 (rekursiv) und TR2 aus. Die inserted- und die deleted-Tabelle für einen bestimmten Trigger enthalten Zeilen, die nur der UPDATE-Anweisung entsprechen, die den Trigger aufgerufen hat.

Hinweis:
Das obige Verhalten tritt nur dann ein, wenn die RECURSIVE_TRIGGERS-Einstellung mithilfe von ALTER DATABASE aktiviert wurde. Es gibt keine vorgeschriebene Reihenfolge für die Ausführung mehrerer, für ein bestimmtes Ereignis definierter Trigger. Jeder Trigger sollte unabhängig sein.

Durch Deaktivierung der RECURSIVE_TRIGGERS-Einstellung werden nur die direkten Rekursionen verhindert. Um die indirekte Rekursion zu deaktivieren, legen Sie die Serveroption nested triggers mithilfe von sp_configure auf 0 fest.

Führt einer der Trigger eine ROLLBACK TRANSACTION-Anweisung aus, werden unabhängig von der Schachtelungsebene keine weiteren Trigger ausgeführt.

Geschachtelte Trigger

Trigger können maximal 32 Ebenen tief geschachtelt werden. Falls ein Trigger eine Tabelle ändert, für die es einen anderen Trigger gibt, wird der zweite Trigger aktiviert und kann dann seinerseits einen dritten Trigger aufrufen usw. Wenn ein Trigger in der Kette eine Endlosschleife auslöst, wird die zulässige Schachtelungsebenenzahl überschritten und der Trigger abgebrochen. Um geschachtelte Trigger zu deaktivieren, legen Sie die Option nested triggers von sp_configure auf 0 (deaktiviert) fest. In der Standardkonfiguration sind geschachtelte Trigger zulässig. Wenn geschachtelte Trigger ausgeschaltet wurden, sind rekursive Trigger ebenfalls deaktiviert, unabhängig von der durch ALTER DATABASE festgelegten RECURSIVE_TRIGGERS-Einstellung.

Hinweis:
Wenn ein Transact-SQL-Trigger verwalteten Code ausführt, indem er auf eine CLR-Routine, einen -Typ oder ein -Aggregat verweist, zählt dieser Verweis als eine Ebene der auf 32 begrenzten Schachtelungsebenen. Methoden, die innerhalb des verwalteten Codes aufgerufen wurden, werden nicht auf diese Schachtelungsgrenze angerechnet.

Verzögerte Namensauflösung

SQL Server lässt zu, dass gespeicherte Transact-SQL-Prozeduren, -Trigger und -Batches auf Tabellen verweisen, die zur Kompilierzeit noch nicht vorhanden sind. Diese Option wird verzögerte Namensauflösung genannt. Wenn die gespeicherten Transact-SQL Prozeduren, Trigger oder Batches jedoch auf eine Tabelle verweisen, die in der gespeicherten Prozedur oder dem Trigger definiert ist, wird bei der Erstellung nur dann eine Warnung ausgegeben, wenn der Kompatibilitätsgrad auf 65 festgelegt ist. Eine Warnung wird zur Kompilierzeit ausgegeben, wenn ein Batch verwendet wird. Zur Laufzeit wird eine Fehlermeldung zurückgegeben, wenn die Tabelle, auf die verwiesen wird, nicht vorhanden ist. Weitere Informationen finden Sie unter Verzögerte Namensauflösung und Kompilierung.

Berechtigungen

Zum Erstellen eines DML-Triggers ist die ALTER-Berechtigung für die Tabelle oder Sicht erforderlich, für die der Trigger erstellt wird.

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

Beispiele

A. Verwenden eines DML-Triggers mit einer Erinnerungsmeldung

Der folgende DML-Trigger gibt jedes Mal, wenn jemand versucht, Daten in die Customer-Tabelle einzufügen bzw. vorhandene Daten zu ändern, eine Meldung an den Client aus.

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. Verwenden eines DML-Triggers mit einer E-Mail-Erinnerungsnachricht

Im folgenden Beispiel wird eine E-Mail-Nachricht an die angegebene Person (MaryM) gesendet, wenn die Customer-Tabelle geändert wird.

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. Verwenden eines DML-AFTER-Triggers zum Erzwingen einer Geschäftsregel zwischen den Tabellen PurchaseOrderHeader und Vendor

Da sich CHECK-Einschränkungen nur auf Spalten beziehen können, für die die Einschränkung auf Spalten- oder Tabellenebene definiert wurde, müssen tabellenübergreifende Einschränkungen (in diesem Fall Geschäftsregeln) als Trigger definiert werden.

Im folgenden Beispiel wird ein DML-Trigger erstellt. Der Trigger überprüft die Bonität eines Herstellers, wenn versucht wird, eine neue Bestellung in die PurchaseOrderHeader-Tabelle einzufügen. Um die Bonität des Herstellers zu ermitteln, muss auf die Vendor-Tabelle verwiesen werden. Ist die Bonität zu niedrig, wird eine Meldung angezeigt, und die Bestellung wird nicht eingefügt.

Hinweis:
Beispiele für DML-AFTER-Trigger, die mehrere Zeilen aktualisieren, finden Sie unter Trigger bei mehrzeiligen Operationen. Beispiele für DML-INSTEAD OF INSERT-Trigger finden Sie unter INSTEAD OF INSERT-Trigger.

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. Verwenden der verzögerten Namensauflösung

Im folgenden Beispiel werden zwei DML-Trigger erstellt, um die verzögerte Namensauflösung zu veranschaulichen.

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. Verwenden eines DDL-Triggers mit Datenbankbereich

Im folgenden Beispiel wird ein DDL-Trigger verwendet, um zu verhindern, dass ein Synonym aus einer Datenbank gelöscht wird.

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. Verwenden eines DDL-Triggers mit Serverbereich

Im folgenden Beispiel wird ein DDL-Trigger verwendet, um eine Meldung auszugeben, wenn ein CREATE DATABASE-Ereignis auf der aktuellen Serverinstanz auftritt. Mithilfe der EVENTDATA-Funktion wird der Text der entsprechenden Transact-SQL-Anweisung abgerufen.

Hinweis:
Weitere Beispiele für die Verwendung von EVENTDATA in DDL-Triggern finden Sie unter Verwenden der EVENTDATA-Funktion.

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. Verwenden eines LOGON-Triggers

Im folgenden Beispiel für LOGON-Trigger wird ein Anmeldeversuch bei SQL Server als Mitglied des Anmeldenamens login_test abgewiesen, wenn unter diesem Anmeldenamen bereits drei Benutzersitzungen ausgeführt werden.

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. Anzeigen der Ereignisse, die einen Trigger auslösen

Im folgenden Beispiel wird die sys.triggers- und die sys.trigger_events-Katalogsicht abgefragt, um zu ermitteln, welche Transact-SQL-Sprachereignisse bewirken, dass der safety-Trigger ausgelöst wird. safety wurde im vorherigen Beispiel erstellt.

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

Siehe auch

Verweis

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)

Andere Ressourcen

Erstellen von gespeicherten Prozeduren (Datenbankmodul)
Programmieren von CLR-Triggern
Verwenden von Bezeichnern als Objektnamen
Abrufen von Informationen zu DML-Triggern
Abrufen von Informationen zu DLL-Triggern
Steuern von Einschränkungen, Identitäten und Triggern mithilfe von NOT FOR REPLICATION
Verwenden von Datentypen mit umfangreichen Werten

Hilfe und Informationen

Informationsquellen für SQL Server 2005

Änderungsverlauf

Version	Verlauf
12. Dezember 2006	Neuer Inhalt: Es wurden im gesamten Thema Informationen zu LOGON-Triggern hinzugefügt, die in SQL Server 2005 Service Pack 2 aufgenommen wurden.
17. Juli 2006	Geänderter Inhalt: Beispiele E und F wurden aktualisiert.
14. April 2006	Neuer Inhalt: Im Abschnitt mit den Hinweisen wurde ein wichtiger Hinweis hinzugefügt, der das Testen der Antworten von DDL-Triggern auf die Ausführung gespeicherter Prozeduren empfiehlt. Dem Beispiel C wurde ein Hinweis mit Links zu Beispielen für DML-AFTER-Trigger, die mehrere Zeilen aktualisieren, und für DML-INSTEAD OF INSERT-Trigger hinzugefügt. Dem Beispiel F wurde ein Hinweis mit einem Link zu weiteren Beispielen für das Verwenden von EVENTDATA in DDL-Triggern hinzugefügt. Geänderter Inhalt: Beispiel B wurde auf die Verwendung von Datenbank-E-Mail aktualisiert.
05. Dezember 2005	Neuer Inhalt: Hinzugefügt, dass DML-Trigger nicht für lokale oder globale temporäre Tabellen definiert werden können. Hinzugefügt, dass DDL-Trigger nicht als Antwort auf Ereignisse ausgelöst werden, die sich auf lokale oder globale temporäre Tabellen und gespeicherte Prozeduren auswirken.