Freigeben über

CREATE TRIGGER (Transact-SQL)

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed Instance

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, UPDATEoder DELETE Anweisungen in einer Tabelle oder Ansicht. Diese Trigger werden ausgelöst, sobald ein beliebiges gültiges Ereignis ausgelöst wird, unabhängig davon, ob Tabellenzeilen betroffen sind oder nicht. Weitere Informationen finden Sie unter DML Triggers.

DDL-Trigger werden als Reaktion auf verschiedene DDL-Ereignisse (Data Definition Language) ausgeführt. Diese Ereignisse entsprechen in erster Linie Transact-SQL CREATE, ALTERAnweisungen und DROP bestimmten gespeicherten Systemprozeduren, die DDL-ähnliche Vorgänge ausführen.

Die Anmeldung löst als Reaktion auf das LOGON Ereignis aus, das ausgelöst wird, wenn die Sitzung eines Benutzers eingerichtet wird. Sie können Trigger direkt aus Transact-SQL-Anweisungen oder aus Methoden von Assemblys erstellen, die in der Microsoft .NET Framework-CLR (Common Language Runtime) erstellt und auf eine Instanz von SQL Server hochgeladen werden. Mit SQL Server können Sie mehrere Trigger für jede konkrete Anweisung erstellen.

Wichtig

Bösartiger Code innerhalb von Triggern kann unter ausgeweiteten Privilegien ausgeführt werden. Weitere Informationen zum Verringern dieser Bedrohung finden Sie unter Verwalten der Triggersicherheit.

Hinweis

Die Integration der .NET Framework-CLR in SQL Server wird in diesem Artikel erläutert. Die CLR-Integration gilt nicht für Azure SQL-Datenbank.

Transact-SQL-Syntaxkonventionen

Syntax

SQL Server-Syntax

Trigger für eine INSERTUPDATETabelle oder DELETE Ansicht (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

Trigger für eine INSERTUPDATETabelle oder DELETE Anweisung (DML-Trigger für speicheroptimierte Tabellen):

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 für einen CREATEDDL-Trigger (DDL-Trigger REVOKEGRANTUPDATEALTERDROPDENY):

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 für ein LOGON Ereignis (Anmeldetrigger):

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-Datenbanksyntax

Trigger für eine INSERTUPDATETabelle oder DELETE Ansicht (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 ]

Trigger für einen CREATEDDL-Trigger (DDL-Trigger REVOKEGRANTUPDATE STATISTICSALTERDROPDENY):

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 ]

Argumente

ODER ÄNDERN

Gilt für: SQL Server 2016 (13.x) SP1 und höhere Versionen und Azure SQL-Datenbank

Ändert den Trigger nur, wenn dieser bereits vorhanden ist.

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 kann für DDL- oder LOGON-Trigger nicht angegeben werden.

trigger_name

Der Name des Triggers. Ein trigger_name muss den Regeln für Bezeichner entsprechen, außer dass trigger_name nicht mit # oder ##beginnen kann.

Tisch | ansehen

Die Tabelle oder Sicht, für die der DML-Trigger ausgeführt wird. Diese Tabelle oder Sicht wird manchmal als Triggertabelle oder Triggersicht bezeichnet. Die Angabe des vollqualifizierten Namens der Tabelle oder Sicht ist optional. Sie können nur über einen INSTEAD OF Trigger auf eine Ansicht verweisen. Sie können DML-Trigger nicht für lokale oder globale temporäre Tabellen definieren.

DATENBANK

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.

ALLE 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.

MIT VERSCHLÜSSELUNG

Verdeckt den Text der CREATE TRIGGER Anweisung. Die Verwendung WITH ENCRYPTION verhindert, dass der Trigger als Teil der SQL Server-Replikation veröffentlicht wird. WITH ENCRYPTION kann für CLR-Trigger nicht angegeben werden.

AUSFÜHREN ALS

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.

Diese Option ist für Trigger in speicheroptimierten Tabellen erforderlich.

Weitere Informationen finden Sie unter EXECUTE AS-Klausel.

Native-Kompilierung

Gibt an, dass die Trigger nativ kompiliert werden.

Diese Option ist für Trigger in speicheroptimierten Tabellen erforderlich.

SCHEMABINDING

Stellt sicher, dass Tabellen, auf die durch einen Trigger verwiesen wird, nicht gelöscht oder geändert werden können.

Diese Option ist für Trigger in speicheroptimierten Tabellen erforderlich und wird nicht für Trigger in herkömmlichen Tabellen unterstützt.

FOR | NACH

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

Sie können Trigger für Ansichten nicht definieren AFTER .

STATT

Gibt an, dass der DML-Trigger anstelle der auslösenden SQL-Anweisung gestartet wird, wodurch die Aktionen der auslösenden Anweisungen überschrieben werden. Sie können für DDL- oder Anmeldetrigger nicht angeben INSTEAD OF .

Sie können höchstens einen INSTEAD OF Auslöser für INSERTUPDATEeine Tabelle oder DELETE Ansicht definieren. Sie können ansichten auch für Ansichten definieren, in denen jede Ansicht über einen eigenen INSTEAD OF Trigger verfügt.

Sie können trigger nicht für aktualisierbare Ansichten definieren INSTEAD OF , die verwendet werden WITH CHECK OPTION. Dies führt zu einem Fehler, wenn ein INSTEAD OF Trigger einer aktualisierten Ansicht WITH CHECK OPTION hinzugefügt wird. Sie entfernen diese Option, indem Sie diese Option verwenden ALTER VIEW , bevor Sie den INSTEAD OF Trigger definieren.

{ [ 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. Geben Sie mindestens eine Option an. Verwenden Sie diese Optionen in der Triggerdefinition in beliebiger Kombination und Reihenfolge.

Für INSTEAD OF Trigger können Sie die Option für Tabellen, die DELETE eine referenzielle Beziehung aufweisen, nicht verwenden, indem Sie eine Kaskadenaktion ON DELETEangeben. Auf ähnliche Weise ist die UPDATE Option für Tabellen mit einer referenziellen Beziehung nicht zulässig und gibt eine Kaskadeaktion ON UPDATEan.

MIT ANFÜGE

Gilt für: SQL Server 2008 (10.0.x) bis SQL Server 2008 R2 (10.50.x).

Gibt an, dass ein weiterer Trigger eines vorhandenen Typs hinzugefügt werden soll. WITH APPEND kann nicht mit INSTEAD OF Triggern verwendet werden oder wenn ein AFTER Trigger explizit angegeben ist. Verwenden Sie WITH APPEND aus Gründen der Abwärtskompatibilität nur, wenn FOR angegeben wird, ohne INSTEAD OF oder AFTER. Sie können nicht angeben WITH APPEND , ob sie verwendet EXTERNAL NAME werden (d. h., wenn der Trigger ein CLR-Trigger ist).

event_type

Dies ist der Name eines Transact-SQL-Sprachereignisses, nach dessen Start ein DDL-Trigger ausgelöst wird. Gültige Ereignisse für DDL-Trigger werden unter DDL-Ereignisse aufgeführt.

event_group

Dies ist der Name einer vordefinierten Gruppe von Transact-SQL-Sprachereignissen. Der DDL-Trigger wird nach dem Start eines beliebigen Transact-SQL-Sprachereignisses ausgelöst, das zu event_group gehört. Gültige Ereignisgruppen für DDL-Trigger werden unter DDL-Ereignisgruppen aufgeführt.

Nachdem die CREATE TRIGGER Ausführung abgeschlossen ist, fungiert event_group auch als Makro, indem die Ereignistypen hinzugefügt werden, die es in der sys.trigger_events Katalogansicht abdeckt.

NICHT FÜR REPLIKATION

Gibt an, dass der Trigger nicht ausgeführt werden sollte, wenn ein Replikations-Agent die vom Trigger betroffene Tabelle ändert.

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 dient zum Überprüfen oder Ändern von Daten basierend auf einer Datenänderungs- oder Definitionsanweisung. Der Trigger sollte keine Daten an den Benutzer zurückgeben. Die Transact-SQL-Anweisungen in einem Trigger enthalten häufig Sprachkonstrukte zur Ablaufsteuerung.

DML-Trigger verwenden die gelöschten und eingefügten logischen (konzeptionellen) Tabellen. 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 gelöschten und eingefügten Tabellen enthalten die alten Werte oder neue Werte der Zeilen, die von der Benutzeraktion geändert werden können. Um beispielsweise alle Werte in der deleted-Tabelle abzurufen, verwenden Sie:

SELECT * FROM deleted;

Weitere Informationen finden Sie unter Verwenden der eingefügten und gelöschten Tabellen.

DDL und Anmeldetrigger erfassen Informationen zum auslösenden Ereignis mithilfe der EVENTDATA-Funktion . Weitere Informationen finden Sie unter Verwenden der EVENTDATA-Funktion.

SQL Server ermöglicht die Aktualisierung von Text-, ntext- oder Bildspalten über den INSTEAD OF Auslöser für Tabellen oder Ansichten.

Wichtig

Die Datentypen ntext, text und image werden in einer künftigen Version von 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) ". Beide AFTER und INSTEAD OF Trigger unterstützen varchar(max)-, nvarchar(max)- und varbinary(max)- Daten in den eingefügten und gelöschten Tabellen.

Bei Triggern für speicheroptimierte Tabellen ist die einzige sql_statement auf oberster Ebene zulässig, ein ATOMIC Block. Der im ATOMIC Block zulässige T-SQL-Code ist durch die in systemeigenen Procs zulässigen T-SQL beschränkt.

<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 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. Wenn die Klasse über einen namespacequalifizierten Namen verfügt, der zum Trennen von Namespaceteilen verwendet . wird, muss der Klassenname durch [] oder " Trennzeichen getrennt werden. Bei der Klasse darf es sich nicht um eine geschachtelte Klasse handeln.

Hinweis

Standardmäßig ist die Möglichkeit, dass SQL Server CLR-Code ausführt, deaktiviert. Sie können Datenbankobjekte erstellen, ändern und ablegen, die auf verwaltete Codemodule verweisen, diese Verweise werden jedoch nicht in einer Instanz von SQL Server ausgeführt, es sei denn, die Option "clr enabled " ist mit sp_configure aktiviert.

Hinweise für DML-Trigger

DML-Trigger werden häufig zum Erzwingen von Geschäftsregeln und Datenintegrität verwendet. SQL Server stellt deklarative referenzielle Integrität (DRI) über die ALTER TABLE und CREATE TABLE Anweisungen bereit. 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 die referenzielle Integrität zu erzwingen, verwenden Sie die PRIMARY KEY Und-Einschränkungen FOREIGN KEY in ALTER TABLE und CREATE TABLE. Wenn Einschränkungen in der Triggertabelle vorhanden sind, werden sie überprüft, nachdem der INSTEAD OF Trigger ausgeführt wurde und bevor der AFTER Trigger ausgeführt wird. Wenn die Einschränkungen verletzt werden, werden die INSTEAD OF Triggeraktionen zurückgesetzt, und der AFTER Trigger wird nicht ausgelöst.

Sie können die ersten und letzten AFTER Trigger angeben, die in einer Tabelle ausgeführt werden sollen.sp_settriggerorder Sie können für jede INSERTUPDATEDELETE Tabelle nur einen ersten und einen letzten AFTER Trigger angeben. Wenn in derselben Tabelle andere AFTER Trigger vorhanden sind, werden sie zufällig ausgeführt.

Wenn eine ALTER TRIGGER Anweisung einen ersten oder letzten Auslöser ändert, wird das erste oder letzte Attribut, das für den geänderten Auslöser festgelegt ist, gelöscht, und Sie müssen den Bestellwert mithilfe sp_settriggerordervon . zurücksetzen.

Ein AFTER Trigger wird erst ausgeführt, nachdem die 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. Ein Auslösen AFTER eines INSTEAD OF Triggers in derselben Tabelle wird nicht rekursiv ausgelöst.

Wenn ein für eine Tabelle definierter INSTEAD OF Auslöser eine Anweisung für die Tabelle ausführt, die den INSTEAD OF Trigger in ordnungsmäßiger Weise auslösen würde, wird der Trigger nicht rekursiv aufgerufen. Stattdessen verarbeitet die Anweisung so, als ob die Tabelle keinen INSTEAD OF Trigger hatte und die Kette von Einschränkungsvorgängen startet und AFTER Ausführungen auslöst. Wenn beispielsweise ein Trigger als INSTEAD OF INSERT Trigger für eine Tabelle definiert ist. Und wenn der Trigger eine INSERT Anweisung in derselben Tabelle ausführt, ruft die INSERT vom INSTEAD OF Trigger gestartete Anweisung den Trigger nicht erneut auf. Der INSERT vom Trigger gestartete Startet den Prozess der Ausführung von Einschränkungsaktionen und löst alle AFTER INSERT für die Tabelle definierten Trigger aus.

Wenn ein in einer Ansicht definierter INSTEAD OF Auslöser eine Anweisung für die Ansicht ausführt, die den INSTEAD OF Trigger in ordnungsmäßiger Weise auslösen würde, wird er nicht rekursiv 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 Modify Data Through a View (Ändern von Daten über eine Sicht).

Wenn beispielsweise ein Trigger als INSTEAD OF UPDATE Trigger für eine Ansicht definiert ist. Und der Trigger führt eine UPDATE Anweisung aus, die auf dieselbe Ansicht verweist. Die UPDATE vom INSTEAD OF Trigger gestartete Anweisung ruft den Trigger nicht erneut auf. Der UPDATE vom Trigger gestartete Vorgang wird für die Ansicht so verarbeitet, als hätte die Ansicht keinen INSTEAD OF Trigger. Die von der UPDATE Tabelle geänderten Spalten müssen in eine einzelne Basistabelle aufgelöst werden. Jede Änderung an einer zugrunde liegenden Basistabelle beginnt die Kette der Anwendung von Einschränkungen und Auslösen AFTER von Triggern, die für die Tabelle definiert sind.

Testen auf AKTUALISIERUNGs- oder EINFÜGEaktionen für bestimmte Spalten

Sie können einen Transact-SQL Trigger entwerfen, um bestimmte Aktionen basierend auf UPDATE bestimmten Spalten oder INSERT Änderungen an bestimmten Spalten auszuführen. Verwenden Sie UPDATE oder COLUMNS_UPDATED im Textkörper des Triggers für diesen Zweck. UPDATE() Tests für UPDATE eine Spalte oder INSERT Versuche für eine Spalte. COLUMNS_UPDATED Tests für UPDATE oder INSERT Aktionen, die auf mehreren Spalten ausgeführt werden. Diese Funktion gibt ein Bitmuster zurück, das angibt, welche Spalten eingefügt oder aktualisiert wurden.

Triggerbeschränkungen

CREATE TRIGGER muss die erste Anweisung im Batch sein und kann nur auf eine Tabelle angewendet werden.

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.

Dieselbe Triggeraktion kann für mehrere Benutzeraktionen (z. B INSERT . und UPDATE) in derselben CREATE TRIGGER Anweisung definiert werden.

INSTEAD OF DELETE / INSTEAD OF UPDATE Trigger können nicht für eine Tabelle definiert werden, die über einen Fremdschlüssel verfügt, der eine Kaskade für DELETE/UPDATE die definierte Aktion enthält.

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 eines Auslösens an eine Anwendung zurückgegeben werden, schließen Sie keine SELECT Anweisungen ein, die Ergebnisse zurückgeben oder Anweisungen, die variable Zuordnungen in einem Trigger ausführen. Ein Trigger, der entweder SELECT Anweisungen enthält, die Ergebnisse an den Benutzer zurückgeben, oder Anweisungen, die variable Zuweisungen ausführen, erfordert eine spezielle Behandlung. Sie müssten die zurückgegebenen Ergebnisse in alle Anwendungsprogramme schreiben, in denen Änderungen an der Triggertabelle zulässig sind. Wenn die Variable zuordnung in einem Trigger erfolgen muss, verwenden Sie eine SET NOCOUNT Anweisung am Anfang des Triggers, um die Rückgabe von Resultsets zu verhindern.

Obwohl eine TRUNCATE TABLE Anweisung eine DELETE Anweisung in Kraft ist, wird kein Trigger aktiviert, da der Vorgang keine einzelnen Zeilenlöschvorgänge protokolliert. Allerdings müssen sich nur benutzer mit Berechtigungen zum Ausführen einer TRUNCATE TABLE Anweisung gedanken darüber machen, einen Trigger auf diese Weise versehentlich zu umgehen DELETE .

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
  • RESTORE DATABASE
  • RESTORE LOG
  • RECONFIGURE

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

  • CREATE INDEX (einschließlich CREATE SPATIAL INDEX und CREATE XML INDEX)
  • ALTER INDEX
  • DROP INDEX
  • DROP TABLE
  • DBCC DBREINDEX
  • ALTER PARTITION FUNCTION
  • ALTER TABLE bei Verwendung der folgenden Aktionen:
    • Hinzufügen, Ändern oder Löschen von Spalten
    • Wechseln zwischen Partitionen
    • Hinzufügen oder Ablegen PRIMARY KEY oder UNIQUE Einschränkungen.

Hinweis

Da SQL Server benutzerdefinierte Trigger in Systemtabellen nicht unterstützt, empfehlen wir, dass Sie keine benutzerdefinierten Trigger für Systemtabellen erstellen.

Optimieren von DML-Triggern

Trigger funktionieren in Transaktionen (impliziert oder anderweitig), und während sie offen sind, sperren sie Ressourcen. Die Sperre bleibt erhalten, bis die Transaktion bestätigt (mit COMMIT) oder abgelehnt wird (mit a ROLLBACK). Je länger ein Trigger ausgeführt wird, desto höher ist die Wahrscheinlichkeit, dass ein anderer Prozess blockiert wird. Schreiben Sie Trigger daher so, dass ihre Dauer so gering wie möglich ist. Eine Möglichkeit, die Dauer zu reduzieren, ist, einen Trigger freizugeben, wenn eine DML-Anweisung 0 (null) Zeilen ändert.

Zum Freigeben des Triggers für einen Befehl, der keine Zeilen ändert, verwenden Sie die Systemvariable ROWCOUNT_BIG.

Der folgende T-SQL-Codeausschnitt veranschaulicht die Freigabe des Triggers für einen Befehl, der keine Zeilen ändert. Dieser Code sollte am Anfang jedes DML-Triggers vorhanden sein:

IF (ROWCOUNT_BIG() = 0)
RETURN;

Hinweise für DDL-Trigger

DDL-Trigger starten, ebenso wie Standardtrigger, gespeicherte Prozeduren als Reaktion auf ein Ereignis. Im Gegensatz zu Standardtriggern werden sie jedoch nicht als Reaktion auf UPDATEINSERT, oder Anweisungen in einer Tabelle oder DELETE Ansicht ausgeführt. Stattdessen führen sie sie primär als Reaktion auf DDL-Anweisungen (Data Definition Language, Datendefinitionssprache) aus. Die Anweisungstypen umfassen CREATE, , ALTER, DROP, GRANT, DENY, und REVOKEUPDATE STATISTICS. 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. Die Anweisung und die sp_addtype gespeicherten sp_rename Prozeduren lösen beispielsweise einen DDL-Trigger aus, CREATE TYPE der für ein CREATE_TYPE Ereignis erstellt wird.

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 haben DDL-Trigger keinen Schemabereich. Sie können also keine Funktionen wie OBJECT_ID, OBJECT_NAME, , OBJECTPROPERTYund OBJECTPROPERTYEX zum Abfragen von Metadaten zu DDL-Triggern verwenden. Verwenden Sie stattdessen die Katalogsichten. Weitere Informationen finden Sie unter Abrufen von Informationen zu DDL-Triggern.

Hinweis

DDL-Trigger im Gültigkeitsbereich des Servers werden im Objekt-Explorer von SQL Server Management Studio im Ordner Trigger angezeigt. Dieser Ordner befindet sich unter dem Ordner Serverobjekte . DDL-Trigger im Datenbankbereich erscheinen im Ordner "Datenbanktrigger". Dieser Ordner befindet sich unter dem Ordner Programmierbarkeit der entsprechenden Datenbank.

Anmeldetrigger

Anmeldetrigger führen gespeicherte Prozeduren als Reaktion auf ein LOGON Ereignis aus. Dieses Ereignis findet statt, wenn eine Benutzersitzung mit einer Instanz von SQL Server hergestellt wird. Logon-Trigger werden ausgelöst, nachdem die Authentifizierungsphase der Anmeldung abgeschlossen ist, aber bevor die Benutzersitzung hergestellt wird. Daher werden alle Nachrichten, die innerhalb des Triggers stammen, die in der Regel den Benutzer erreichen, z. B. Fehlermeldungen und Nachrichten aus der PRINT Anweisung, in das SQL Server-Fehlerprotokoll umgeleitet. Weitere Informationen finden Sie unter Anmeldetrigger.

Logon-Trigger werden nicht ausgelöst, wenn die Authentifizierung nicht ausgeführt werden kann.

Verteilte Transaktionen werden in einem Logon-Trigger nicht unterstützt. Fehler 3969 wird zurückgegeben, wenn ein Logon-Trigger ausgelöst wird, der eine verteilte Transaktion enthält.

Deaktivieren eines Anmeldetriggers

Ein Logon-Trigger kann effektiv erfolgreiche Verbindungen zu Datenbank-Engine für alle Benutzer verhindern, einschließlich Elementen der festen Serverrolle sysadmin . Wenn ein Anmeldetrigger Verbindungen verhindert, können Mitglieder der sysadmin fixed-Serverrolle mithilfe der dedizierten Administratorverbindung eine Verbindung herstellen oder das Datenbankmodul im minimalen Konfigurationsmodus (-f) starten. Weitere Informationen finden Sie unter Startoptionen des Datenbankmoduldiensts.

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. Trigger, die Resultsets zurückgeben, können unerwartetes Verhalten in Anwendungen verursachen, die nicht für die Arbeit mit ihnen konzipiert sind. Vermeiden Sie deshalb bei Neuentwicklungen, Resultsets aus Triggern zurückzugeben, und planen Sie die Änderung von Anwendungen, in denen dies derzeit geschieht. Legen Sie die Option Ergebnisse von Triggern nicht zulassen auf 1 fest, um zu verhindern, dass Trigger Resultsets zurückgeben.

Logon-Trigger lassen nie zu, dass Resultsets zurückgegeben werden. Dieses Verhalten ist nicht konfigurierbar. Wenn ein Logon-Trigger ein Resultset generiert, kann der Trigger nicht gestartet werden, und der Anmeldeversuch, der den Trigger ausgelöst hat, wird abgelehnt.

Mehrere Trigger

Mit SQL Server können Sie mehrere Trigger für jede DML, DDL oder LOGON jedes Ereignis erstellen. Wenn beispielsweise CREATE TRIGGER FOR UPDATE für eine Tabelle ausgeführt wird, die bereits über einen UPDATE Trigger verfügt, wird ein zusätzlicher Updatetrigger erstellt. In früheren Versionen von SQL Server ist für jede INSERTUPDATEDELETE Tabelle nur ein Auslöser oder datenänderungsereignis zulässig.

Rekursive Trigger

SQL Server unterstützt auch rekursive Aufrufe von Triggern, wenn die RECURSIVE_TRIGGERS Einstellung mit ALTER DATABASEaktiviert ist.

Rekursive Trigger ermöglichen die folgenden Arten von Rekursion:

  • Indirekte Rekursion: Mit indirekter Rekursion aktualisiert eine Anwendungsaktualisierungstabelle T1. Dadurch wird die Tabelle T2ausgelöstTR1, aktualisiert. Lösen Sie T2 dann die Tabelle T1aus und aktualisiert sie.

  • Direkte Rekursion: In direkter Rekursion aktualisiert T1die Tabelle der Anwendung. Dadurch wird die Tabelle T1ausgelöstTR1, aktualisiert. Da die Tabelle T1 aktualisiert wurde, wird sie erneut ausgelöst TR1 usw.

Im folgenden Beispiel wird sowohl die indirekte als auch die direkte Trigger-Rekursion verwendet. Es wird davon ausgegangen, TR1 dass zwei Aktualisierungstrigger und TR2in der Tabelle T1definiert sind. Aktualisieren der Tabelle T1 rekursiv auslösenTR1. Eine UPDATE Anweisung wird jeweils TR1 und TR2 einmal ausgeführt. Darüber hinaus löst der Start von TR1 Triggern die Ausführung von TR1 (rekursiv) und TR2. Die eingefügten und gelöschten Tabellen für einen bestimmten Trigger enthalten Zeilen, die nur der UPDATE Anweisung entsprechen, die den Trigger aufgerufen hat.

Hinweis

Das vorherige Verhalten tritt nur auf, wenn die RECURSIVE_TRIGGERS Einstellung mithilfe ALTER DATABASEvon . 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 Deaktivieren der RECURSIVE_TRIGGERS Einstellung werden nur direkte Rekursionen verhindert. Um auch die indirekte Rekursion zu deaktivieren, legen Sie die Serveroption für geschachtelte Trigger auf 0 fest.sp_configure

Wenn eines der Trigger einen ROLLBACK TRANSACTION, unabhängig von der Schachtelungsebene, ausführt, werden keine weiteren Trigger ausgeführt.

Geschachtelte Trigger

Sie können Trigger auf ein Maximum von 32 Ebenen schachteln. 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. Wenn ein Transact-SQL-Trigger verwalteten Code startet, indem er auf eine CLR-Routine, einen CLR-Typ oder ein CLR-Aggregat verweist, zählt dieser Verweis als eine Ebene der auf 32 begrenzten Schachtelungsebenen. Methoden, die aus verwaltetem Code aufgerufen werden, werden nicht mitgezählt.

Um geschachtelte Trigger zu deaktivieren, legen Sie die Option für geschachtelte Trigger auf sp_configure 0 (deaktiviert) fest. Die Standardkonfiguration unterstützt geschachtelte Trigger. Wenn geschachtelte Trigger deaktiviert sind, werden rekursive Trigger auch deaktiviert, obwohl die RECURSIVE_TRIGGERS Einstellung verwendet ALTER DATABASEwird.

Der erste AFTER in einem INSTEAD OF Trigger geschachtelte Trigger wird ausgelöst, auch wenn die geschachtelte Serverkonfigurationsoption 0 ist. Unter dieser Einstellung werden die späteren AFTER Trigger jedoch nicht ausgelöst. Überprüfen Sie Ihre Anwendungen auf geschachtelte Trigger, um festzustellen, ob die Anwendungen Ihren Geschäftsregeln entsprechen, wenn für die Serverkonfigurationsoption geschachtelte Trigger 0 (null) festgelegt ist. Wenn dies nicht der Fall ist, nehmen Sie die entsprechenden Änderungen vor.

Auflösung verzögerter Namen

SQL Server ermöglicht Transact-SQL gespeicherten Prozeduren, Triggern, Funktionen und Batches, um auf Tabellen zu verweisen, die zur Kompilierungszeit nicht vorhanden sind. Diese Option wird verzögerte Namensauflösung genannt.

Berechtigungen

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

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

Beispiele

Ein. Verwenden eines DML-Triggers mit einer Erinnerungsnachricht

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

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

B. Verwenden eines DML-Triggers mit einer Erinnerungs-E-Mail-Nachricht

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

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

Da CHECK Einschränkungen nur auf die Spalten verweisen, für die die Einschränkung auf Spaltenebene oder Tabellenebene definiert ist, müssen Sie alle tabellenübergreifenden Einschränkungen (in diesem Fall Geschäftsregeln) als Trigger definieren.

Im folgenden Beispiel wird ein DML-Trigger in der AdventureWorks2022-Beispieldatenbank erstellt. Bei dem Versuch, eine neue Bestellung in die Tabelle PurchaseOrderHeader einzufügen, überprüft der Trigger, ob die Bonität eines Herstellers gut ist (nicht 5). 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 durchgeführt.

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: 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.

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

Im folgenden Beispiel wird ein DDL-Trigger verwendet, um eine Nachricht zu drucken, wenn ein CREATE DATABASE Ereignis auf der aktuellen Serverinstanz auftritt, und verwendet die EVENTDATA Funktion, um den Text der entsprechenden Transact-SQL-Anweisung abzurufen. Weitere Beispiele, die in DDL-Triggern verwendet werden EVENTDATA , finden Sie unter Verwenden der EVENTDATA-Funktion.

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. Verwenden eines Anmeldetriggers

Das folgende Anmeldetriggerbeispiel verweigert den Versuch, sich bei SQL Server als Mitglied der login_test Anmeldung anzumelden, wenn bereits drei Benutzersitzungen unter dieser Anmeldung ausgeführt werden. Ändern Sie <password> ein sicheres Kennwort.

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. Anzeigen der Ereignisse, die dazu führen, dass ein Trigger ausgelöst wird

Im folgenden Beispiel werden die Katalogsichten sys.triggers und sys.trigger_events abgefragt, um zu ermitteln, welche Transact-SQL-Sprachereignisse bewirken, dass der safety-Trigger ausgelöst wird. Der Auslöser safetywird im Beispiel D erstellt. Verwenden Sie einen DDL-Trigger mit Datenbankbereich.

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

Verwandte Inhalte