CREATE TRIGGER (Transact-.SQL)

  • 30.10.2019
  • 24 Minuten zu lesen
    • W
    • M
    • c
    • r
    • m
    • +16

Gilt für: JaSQL Server (alle unterstützten Versionen) JaAzure SQL Database

Erzeugt einen DML-, DDL- oder Anmeldetrigger. Ein Trigger ist ein spezieller Typ einer gespeicherten Prozedur, die automatisch ausgeführt wird, wenn ein Ereignis im Datenbankserver eintritt. DML-Trigger werden ausgeführt, wenn ein Benutzer versucht, Daten durch ein DML-Ereignis (Data Manipulation Language) zu ändern. DML-Ereignisse sind INSERT-, UPDATE- oder DELETE-Anweisungen für eine Tabelle oder einen View. Diese Trigger werden ausgelöst, wenn ein gültiges Ereignis ausgelöst wird, unabhängig davon, ob Tabellenzeilen betroffen sind oder nicht. Weitere Informationen finden Sie unter DML-Trigger.

DDL-Trigger werden als Reaktion auf eine Vielzahl von Data Definition Language (DDL)-Ereignissen ausgeführt. Diese Ereignisse entsprechen in erster Linie Transact-SQL CREATE-, ALTER- und DROP-Anweisungen sowie bestimmten gespeicherten Systemprozeduren, die DDL-ähnliche Operationen durchführen.

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

Wichtig

Bösartiger Code in Triggern kann mit erweiterten Rechten ausgeführt werden. Weitere Informationen zur Entschärfung dieser Bedrohung finden Sie unter Verwalten der Trigger-Sicherheit.

Hinweis

Die Integration von .NET Framework CLR in SQL Server wird in diesem Artikel behandelt. Die CLR-Integration gilt nicht für Azure SQL Database.

Themenverknüpfungssymbol Transact-SQL-Syntaxkonventionen

SQL Server Syntax

-- SQL Server Syntax -- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger) CREATE TRIGGER trigger_name ON { table | view } ] { FOR | AFTER | INSTEAD OF } { } AS { sql_statement | EXTERNAL NAME <method specifier > } <dml_trigger_option> ::= <method_specifier> ::= assembly_name.class_name.method_name 
-- SQL Server Syntax -- Trigger on an INSERT, UPDATE, or DELETE statement to a -- table (DML Trigger on memory-optimized tables) CREATE TRIGGER trigger_name ON { table } ] { FOR | AFTER } { } AS { sql_statement } <dml_trigger_option> ::= 
-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, -- REVOKE or UPDATE statement (DDL Trigger) CREATE TRIGGER trigger_name ON { ALL SERVER | DATABASE } ] { FOR | AFTER } { event_type | event_group } AS { sql_statement | EXTERNAL NAME < method specifier > } <ddl_trigger_option> ::= 

-- Trigger on a LOGON event (Logon Trigger) CREATE TRIGGER trigger_name ON ALL SERVER ] { FOR| AFTER } LOGON AS { sql_statement | EXTERNAL NAME < method specifier > } <logon_trigger_option> ::= 

Azure SQL-Datenbanksyntax

-- Azure SQL Database Syntax -- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger) CREATE TRIGGER trigger_name ON { table | view } ] { FOR | AFTER | INSTEAD OF } { } AS { sql_statement > } <dml_trigger_option> ::= 
-- Azure SQL Database Syntax -- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, -- REVOKE, or UPDATE STATISTICS statement (DDL Trigger) CREATE TRIGGER trigger_name ON { DATABASE } ] { FOR | AFTER } { event_type | event_group } AS { sql_statement } <ddl_trigger_option> ::= 

Hinweis

Um die Transact-SQL-Syntax für SQL Server 2014 und früher, siehe Dokumentation für frühere Versionen.

Argumente

OR ALTER
Gilt für: Azure SQL Database, SQL Server (ab SQL Server 2016 (13.x) SP1).

Bedingte Änderung des Triggers nur, wenn er bereits existiert.

schema_name
Der Name des Schemas, zu dem ein DML-Trigger gehört. DML-Trigger sind auf das Schema der Tabelle oder des Views beschränkt, auf dem sie erstellt werden. schema_name kann nicht für DDL- oder Logon-Trigger angegeben werden.

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

Tabelle | view
Die Tabelle oder der View, auf dem der DML-Trigger ausgeführt wird. Diese Tabelle oder Ansicht wird manchmal auch als Trigger-Tabelle oder Trigger-Ansicht bezeichnet. Die Angabe des voll qualifizierten Namens der Tabelle oder des Views ist optional. Sie können einen View nur durch einen INSTEAD OF-Trigger referenzieren. Sie können keine DML-Trigger für lokale oder globale temporäre Tabellen definieren.

DATABASE
Wendet den Bereich eines DDL-Triggers auf die aktuelle Datenbank an. Wenn angegeben, wird der Trigger ausgelöst, wenn event_type oder event_group in der aktuellen Datenbank auftritt.

ALL SERVER
Gilt für: SQL Server 2008 und höher.

Wendet den Geltungsbereich eines DDL- oder Anmelde-Triggers auf den aktuellen Server an. Wenn angegeben, wird der Trigger immer ausgelöst, wenn event_type oder event_group irgendwo auf dem aktuellen Server auftritt.

WITH ENCRYPTION
Gilt für: SQL Server 2008 und höher.

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

EXECUTE AS
Spezifiziert den Sicherheitskontext, unter dem der Trigger ausgeführt wird. Ermöglicht Ihnen zu steuern, welches Benutzerkonto die Instanz von SQL Server verwendet, um die Berechtigungen für alle Datenbankobjekte zu überprüfen, auf die der Trigger verweist.

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

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

NATIVE_COMPILATION
Gibt an, dass der Trigger nativ kompiliert ist.

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

SCHEMABINDING
Stellt sicher, dass Tabellen, auf die ein Trigger verweist, nicht gelöscht oder geändert werden können.

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

FOR | AFTER
FOR oder AFTER gibt an, dass der DML-Trigger nur ausgelöst wird, wenn alle in der auslösenden SQL-Anweisung angegebenen Operationen erfolgreich gestartet wurden. Alle referentiellen Kaskadenaktionen und Beschränkungsprüfungen müssen ebenfalls erfolgreich sein, bevor dieser Trigger ausgelöst wird.

Sie können keine AFTER-Trigger auf Views definieren.

INSTEAD OF
Steuert, dass der DML-Trigger anstelle der auslösenden SQL-Anweisung gestartet wird und somit die Aktionen der auslösenden Anweisungen außer Kraft setzt. Sie können INSTEAD OF nicht für DDL- oder Anmelde-Trigger angeben.

Sie können höchstens einen INSTEAD OF-Trigger pro INSERT-, UPDATE- oder DELETE-Anweisung auf einer Tabelle oder einem View definieren. Sie können auch Views auf Views definieren, wobei jeder View seinen eigenen INSTEAD OF-Trigger hat.

Sie können keine INSTEAD OF-Trigger auf aktualisierbaren Views definieren, die WITH CHECK OPTION verwenden. Dies führt zu einem Fehler, wenn ein INSTEAD OF-Trigger zu einer aktualisierbaren Ansicht hinzugefügt wird, für die WITH CHECK OPTION angegeben ist. Sie entfernen diese Option, indem Sie ALTER VIEW verwenden, bevor Sie den INSTEAD OF-Trigger definieren.

{ }
Spezifiziert die Datenänderungsanweisungen, die den DML-Trigger aktivieren, wenn er gegen diese Tabelle oder diesen View versucht wird. Geben Sie mindestens eine Option an. Verwenden Sie eine beliebige Kombination dieser Optionen in beliebiger Reihenfolge in der Trigger-Definition.

Für INSTEAD OF-Trigger können Sie die Option DELETE nicht auf Tabellen verwenden, die eine referentielle Beziehung haben, indem Sie eine Kaskadenaktion ON DELETE angeben. Ebenso ist die Option UPDATE nicht für Tabellen erlaubt, die eine referentielle Beziehung haben und eine Kaskadenaktion ON UPDATE angeben.

Mit APPEND
Gilt für: SQL Server 2008 bis SQL Server 2008 R2.

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

event_type
Der Name eines Transact-SQL-Sprachereignisses, das nach dem Start einen DDL-Trigger auslöst. Gültige Ereignisse für DDL-Trigger sind in DDL-Ereignisse aufgeführt.

event_group
Der Name einer vordefinierten Gruppierung 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 sind in DDL-Ereignisgruppen aufgeführt.

Nach der Ausführung von CREATE TRIGGER fungiert event_group auch als Makro, indem es die Ereignistypen, die es abdeckt, der Katalogansicht sys.trigger_events hinzufügt.

NICHT ZUR REPLIKATION
Gilt für: SQL Server 2008 und höher.

Gibt an, dass der Trigger nicht ausgeführt werden soll, wenn ein Replikationsagent die Tabelle ändert, die am Trigger beteiligt ist.

sql_statement
Die Triggerbedingungen und Aktionen. Triggerbedingungen geben zusätzliche Kriterien an, die bestimmen, ob die versuchten DML-, DDL- oder Anmeldeereignisse dazu führen, dass die Triggeraktionen ausgeführt werden.

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

Trigger können eine beliebige Anzahl und Art von Transact-SQL-Anweisungen enthalten, mit Ausnahmen. Weitere Informationen finden Sie unter Bemerkungen. Ein Trigger ist dazu gedacht, Daten auf der Grundlage einer Datenmodifikation oder Definitionsanweisung zu prüfen oder zu ändern; er sollte keine Daten an den Benutzer zurückgeben. Die Transact-SQL-Anweisungen in einem Trigger enthalten häufig Control-of-Flow-Sprache.

DML-Trigger verwenden die gelöschten und eingefügten logischen (konzeptionellen) Tabellen. Sie sind strukturell ähnlich wie die Tabelle, auf der der Trigger definiert ist, also die Tabelle, auf der die Benutzeraktion versucht wird. Die gelöschten und eingefügten Tabellen enthalten die alten oder neuen Werte der Zeilen, die durch die Benutzeraktion geändert werden können. Um beispielsweise alle Werte in der Tabelle deleted abzurufen, verwenden Sie:

SELECT * FROM deleted; 

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

DDL- und Anmelde-Trigger erfassen Informationen über das auslösende Ereignis, indem sie die Funktion EVENTDATA (Transact-SQL) verwenden. Weitere Informationen finden Sie unter Verwenden der Funktion EVENTDATA.

SQL Server erlaubt die Aktualisierung von Text-, nText- oder Bildspalten durch den INSTEAD OF-Trigger auf Tabellen oder Ansichten.

Wichtig

nText-, Text- und Bilddatentypen werden in einer zukünftigen Version von MicrosoftSQL Server entfernt. Vermeiden Sie die Verwendung dieser Datentypen bei Neuentwicklungen und planen Sie die Änderung von Anwendungen, die diese Typen derzeit verwenden. 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 den eingefügten und gelöschten Tabellen.

Für Trigger auf speicheroptimierten Tabellen ist das einzige erlaubte sql_statement auf oberster Ebene ein ATOMIC-Block. Das erlaubte T-SQL innerhalb des ATOMIC-Blocks ist durch das erlaubte T-SQL innerhalb nativer Procs begrenzt.

< method_specifier >Gilt für: SQL Server 2008 und höher.

Für einen CLR-Trigger wird hier die Methode einer Assembly angegeben, die mit dem Trigger verbunden werden soll. Die Methode darf keine Argumente annehmen und muss void zurückgeben. class_name muss ein gültiger SQL Server-Bezeichner sein und muss als Klasse in der Assembly mit Assembly-Sichtbarkeit existieren. Wenn die Klasse einen Namespace-qualifizierten Namen hat, der ‘.’ verwendet, um Namespace-Teile zu trennen, muss der Klassenname mit den Begrenzungszeichen oder ” ” begrenzt werden. Die Klasse kann keine verschachtelte Klasse sein.

Hinweis

Standardmäßig ist die Fähigkeit von SQL Server, CLR-Code auszuführen, deaktiviert. Sie können Datenbankobjekte erstellen, ändern und löschen, die auf verwaltete Codemodule verweisen, aber diese Verweise werden in einer Instanz von SQL Server nicht ausgeführt, es sei denn, die Option “clr enabled” wird mit sp_configure aktiviert.

Hinweise für DML-Trigger

DML-Trigger werden häufig zur Durchsetzung von Geschäftsregeln und Datenintegrität verwendet. SQL Server bietet deklarative referentielle Integrität (DRI) durch die Anweisungen ALTER TABLE und CREATE TABLE. DRI bietet jedoch keine datenbankübergreifende referentielle Integrität. Referentielle Integrität bezieht sich auf die Regeln über die Beziehungen zwischen den Primär- und Fremdschlüsseln von Tabellen. Um referentielle Integrität zu erzwingen, verwenden Sie die PRIMARY KEY- und FOREIGN KEY-Einschränkungen in ALTER TABLE und CREATE TABLE. Wenn Beschränkungen auf der Triggertabelle vorhanden sind, werden sie nach der Ausführung des INSTEAD OF-Triggers und vor der Ausführung des AFTER-Triggers überprüft. Wenn die Beschränkungen verletzt werden, werden die INSTEAD OF-Trigger-Aktionen zurückgesetzt und der AFTER-Trigger wird nicht abgefeuert.

Sie können den ersten und den letzten AFTER-Trigger, der auf einer Tabelle ausgeführt werden soll, mit sp_settriggerorder angeben. Sie können nur einen ersten und einen letzten AFTER-Trigger für jede INSERT-, UPDATE- und DELETE-Operation auf einer Tabelle angeben. Wenn es andere AFTER-Trigger für dieselbe Tabelle gibt, werden sie zufällig ausgeführt.

Wenn eine ALTER TRIGGER-Anweisung einen ersten oder letzten Trigger ändert, wird das erste oder letzte Attribut, das für den geänderten Trigger festgelegt wurde, verworfen und Sie müssen den Ordnungswert mit sp_settriggerorder zurücksetzen.

Ein AFTER-Trigger wird nur ausgeführt, wenn die auslösende SQL-Anweisung erfolgreich ausgeführt wurde. Diese erfolgreiche Ausführung umfasst alle referenziellen Kaskadenaktionen und Beschränkungsprüfungen, die mit dem aktualisierten oder gelöschten Objekt verbunden sind. Ein AFTER-Trigger löst nicht rekursiv einen INSTEAD OF-Trigger auf derselben Tabelle aus.

Wenn ein auf einer Tabelle definierter INSTEAD OF-Trigger eine Anweisung gegen die Tabelle ausführt, die den INSTEAD OF-Trigger normalerweise erneut auslösen würde, wird der Trigger nicht rekursiv aufgerufen. Stattdessen wird die Anweisung so verarbeitet, als hätte die Tabelle keinen INSTEAD OF-Trigger und startet die Kette von Beschränkungsoperationen und AFTER-Trigger-Ausführungen. Wenn zum Beispiel ein Trigger als INSTEAD OF INSERT-Trigger für eine Tabelle definiert ist. Und der Trigger führt eine INSERT-Anweisung auf derselben Tabelle aus, ruft die vom INSTEAD OF-Trigger gestartete INSERT-Anweisung den Trigger nicht erneut auf. Wenn ein INSTEAD OF-Trigger, der für einen View definiert ist, eine Anweisung gegen den View ausführt, die normalerweise den INSTEAD OF-Trigger erneut auslösen würde, wird er nicht rekursiv aufgerufen. Stattdessen wird die Anweisung als Änderungen gegen die dem View zugrunde liegenden Basistabellen aufgelöst. In diesem Fall muss die View-Definition alle Einschränkungen für einen aktualisierbaren View erfüllen. Eine Definition von aktualisierbaren Views finden Sie unter Ändern von Daten über einen View.

Wenn zum Beispiel ein Trigger als INSTEAD OF UPDATE-Trigger für einen View definiert ist. Und der Trigger führt eine UPDATE-Anweisung aus, die auf dieselbe Ansicht verweist, ruft die vom INSTEAD OF-Trigger gestartete UPDATE-Anweisung den Trigger nicht erneut auf. Das vom Trigger gestartete UPDATE wird gegen den View verarbeitet, als ob der View keinen INSTEAD OF-Trigger hätte. Die durch das UPDATE geänderten Spalten müssen in eine einzige Basistabelle aufgelöst werden. Jede Änderung an einer zugrundeliegenden Basistabelle startet die Kette der Anwendung von Constraints und des Auslösens von AFTER-Triggern, die für die Tabelle definiert sind.

Testen auf UPDATE- oder INSERT-Aktionen für bestimmte Spalten

Sie können einen Transact-SQL-Trigger entwerfen, um bestimmte Aktionen basierend auf UPDATE- oder INSERT-Änderungen an bestimmten Spalten durchzuführen. Verwenden Sie zu diesem Zweck UPDATE() oder COLUMNS_UPDATED im Body des Triggers. UPDATE() testet auf UPDATE- oder INSERT-Versuche an einer Spalte. COLUMNS_UPDATED testet auf UPDATE- oder INSERT-Aktionen, die auf mehreren Spalten ablaufen. Diese Funktion gibt ein Bitmuster zurück, das angibt, welche Spalten eingefügt oder aktualisiert wurden.

Trigger-Einschränkungen

CREATE TRIGGER muss die erste Anweisung im Stapel sein und kann sich nur auf eine Tabelle beziehen.

Ein Trigger wird nur in der aktuellen Datenbank erstellt; ein Trigger kann jedoch Objekte außerhalb der aktuellen Datenbank referenzieren.

Wenn der Name des Triggerschemas angegeben wird, um den Trigger zu qualifizieren, qualifizieren Sie den Tabellennamen auf dieselbe Weise.

Die gleiche Trigger-Aktion kann für mehr als eine Benutzeraktion (z. B. INSERT und UPDATE) in der gleichen CREATE TRIGGER-Anweisung definiert werden.

INSTEAD OF DELETE/UPDATE-Trigger können nicht auf einer Tabelle definiert werden, die einen Fremdschlüssel mit einer Kaskade auf DELETE/UPDATE-Aktion definiert hat.

Eine beliebige SET-Anweisung kann innerhalb eines Triggers angegeben werden. Die gewählte SET-Option bleibt während der Ausführung des Triggers in Kraft und kehrt dann zu ihrer vorherigen Einstellung zurück.

Wenn ein Trigger ausgelöst wird, werden die Ergebnisse an die aufrufende Anwendung zurückgegeben, genau wie bei gespeicherten Prozeduren. Um zu verhindern, dass durch das Auslösen eines Triggers Ergebnisse an eine Anwendung zurückgegeben werden, sollten Sie weder SELECT-Anweisungen, die Ergebnisse zurückgeben, noch Anweisungen, die eine Variablenzuweisung durchführen, in einen Trigger aufnehmen. Ein Trigger, der entweder SELECT-Anweisungen enthält, die Ergebnisse an den Benutzer zurückgeben, oder Anweisungen, die eine Variablenzuweisung durchführen, erfordert eine besondere Behandlung. Sie müssten die zurückgegebenen Ergebnisse in jede Anwendung schreiben, in der Änderungen an der Triggertabelle erlaubt sind. Wenn eine Variablenzuweisung in einem Trigger erfolgen muss, verwenden Sie eine SET NOCOUNT-Anweisung am Anfang des Triggers, um die Rückgabe von Ergebnismengen zu verhindern.

Obwohl eine TRUNCATE TABLE-Anweisung im Grunde eine DELETE-Anweisung ist, aktiviert sie keinen Trigger, da die Operation keine einzelnen Zeilenlöschungen protokolliert. Allerdings müssen sich nur die Benutzer mit der Berechtigung zum Ausführen einer TRUNCATE TABLE-Anweisung Sorgen machen, dass sie auf diese Weise versehentlich einen DELETE-Trigger umgehen.

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

Die folgenden Transact-SQL-Anweisungen sind in einem DML-Trigger nicht erlaubt:

  • ALTER DATABASE
  • CREATE DATABASE
  • DROP DATABASE
  • RESTORE DATABASE
  • RESTORE LOG
  • RECONFIGURE

Außerdem, sind die folgenden Transact-SQL-Anweisungen innerhalb des Körpers eines DML-Triggers nicht erlaubt, wenn er gegen die Tabelle oder den View verwendet wird, die das Ziel der auslösenden Aktion sind.

  • CREATE INDEX (einschließlich CREATE SPATIAL INDEX und CREATE XML INDEX)
  • ALTER INDEX
  • DROP INDEX
  • DROP TABLE
  • DBCC DBREINDEX
  • ALTER PARTITION FUNCTION
  • ALTER TABLE, wenn sie für die folgenden Aktionen verwendet wird:
    • Hinzufügen, Ändern oder Löschen von Spalten.
    • Umschalten von Partitionen.
    • Hinzufügen oder Löschen von PRIMARY KEY- oder UNIQUE-Beschränkungen.

Hinweis

Da SQL Server keine benutzerdefinierten Trigger auf Systemtabellen unterstützt, empfehlen wir, keine benutzerdefinierten Trigger auf Systemtabellen zu erstellen.

Optimierung von DML-Triggern

Trigger arbeiten in Transaktionen (implizit oder anderweitig), und während sie geöffnet sind, sperren sie Ressourcen. Die Sperre bleibt bestehen, bis die Transaktion bestätigt (mit COMMIT) oder verworfen (mit einem ROLLBACK) wird. Je länger ein Trigger läuft, desto höher ist die Wahrscheinlichkeit, dass dann ein anderer Prozess blockiert wird. Schreiben Sie also Trigger so, dass ihre Dauer möglichst gering ist. Eine Möglichkeit, eine kürzere Dauer zu erreichen, besteht darin, einen Trigger freizugeben, wenn eine DML-Anweisung null Zeilen ändert.

Um den Trigger für einen Befehl freizugeben, der keine Zeilen ändert, verwenden Sie die Systemvariable ROWCOUNT_BIG.

Der folgende T-SQL-Codeausschnitt zeigt, wie Sie den Trigger für einen Befehl freigeben, der keine Zeilen ändert. Dieser Code sollte am Anfang jedes DML-Triggers stehen:

IF (ROWCOUNT_BIG() = 0)RETURN;

Hinweise für DDL-Trigger

DDL-Trigger starten wie Standard-Trigger gespeicherte Prozeduren in Reaktion auf ein Ereignis. Aber im Gegensatz zu Standard-Triggern werden sie nicht als Reaktion auf UPDATE-, INSERT- oder DELETE-Anweisungen für eine Tabelle oder einen View ausgeführt. Stattdessen werden sie in erster Linie als Reaktion auf DDL-Anweisungen (Data Definition Language) ausgeführt. Zu den Anweisungstypen gehören CREATE, ALTER, DROP, GRANT, DENY, REVOKE und UPDATE STATISTICS. Bestimmte System-Stored-Procedures, die DDL-ähnliche Operationen ausführen, können auch DDL-Trigger auslösen.

Wichtig

Testen Sie Ihre DDL-Trigger, um ihre Reaktionen auf die Ausführung von System-Stored-Procedures zu bestimmen. Zum Beispiel feuern die CREATE TYPE-Anweisung und die Stored Procedures sp_addtype und sp_rename einen DDL-Trigger, der bei einem CREATE_TYPE-Ereignis erstellt wird.

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

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

Im Gegensatz zu DML-Triggern sind DDL-Trigger nicht auf Schemas beschränkt. Daher können Sie Funktionen wie OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY und OBJECTPROPERTYEX nicht zur Abfrage von Metadaten über DDL-Trigger verwenden. Verwenden Sie stattdessen die Katalogansichten. Weitere Informationen finden Sie unter Abrufen von Informationen über DDL-Trigger.

Hinweis

Server-scoped DDL-Trigger werden im Objekt-Explorer von SQL Server Management Studio im Ordner “Triggers” angezeigt. Dieser Ordner befindet sich unter dem Ordner “Server Objects”. Datenbankübergreifende DDL-Trigger werden im Ordner Database Triggers angezeigt. Dieser Ordner befindet sich unter dem Ordner “Programmierbarkeit” der entsprechenden Datenbank.

Logon-Trigger

Logon-Trigger führen gespeicherte Prozeduren als Reaktion auf ein LOGON-Ereignis aus. Dieses Ereignis tritt ein, wenn eine Benutzersitzung mit einer Instanz von SQL Server aufgebaut wird. Logon-Trigger feuern, nachdem die Authentifizierungsphase der Anmeldung abgeschlossen ist, aber bevor die Benutzersitzung aufgebaut ist. Daher werden alle Meldungen, die innerhalb des Triggers entstehen und normalerweise den Benutzer erreichen würden, wie z. B. Fehlermeldungen und Meldungen von der PRINT-Anweisung, in das SQL Server-Fehlerprotokoll umgeleitet. Weitere Informationen finden Sie unter Logon-Trigger.

Logon-Trigger werden nicht ausgelöst, wenn die Authentifizierung fehlschlägt.

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

Deaktivieren eines Anmeldetriggers

Ein Anmeldetrigger kann effektiv erfolgreiche Verbindungen zur Datenbank-Engine für alle Benutzer, einschließlich Mitglieder der festen Serverrolle sysadmin, verhindern. Wenn ein Anmeldetrigger Verbindungen verhindert, können Mitglieder der festen Serverrolle “sysadmin” eine Verbindung herstellen, indem sie die dedizierte Administratorverbindung verwenden oder die Datenbank-Engine im minimalen Konfigurationsmodus (-f) starten. Weitere Informationen finden Sie unter Startoptionen für den Datenbankmodul-Dienst.

Allgemeine Überlegungen zu Triggern

Ergebnisse zurückgeben

Die Möglichkeit, Ergebnisse von Triggern zurückzugeben, wird in einer zukünftigen Version von SQL Server entfernt. Trigger, die Ergebnismengen zurückgeben, können zu unerwartetem Verhalten in Anwendungen führen, die nicht dafür ausgelegt sind, mit ihnen zu arbeiten. Vermeiden Sie bei Neuentwicklungen die Rückgabe von Ergebnismengen aus Triggern und planen Sie die Änderung von Anwendungen, die dies bereits tun. Um zu verhindern, dass Trigger Ergebnismengen zurückgeben, setzen Sie die Option “Ergebnisse von Triggern verbieten” auf 1.

Anmeldetrigger verbieten immer die Rückgabe von Ergebnismengen und dieses Verhalten ist nicht konfigurierbar. Wenn ein Anmelde-Trigger eine Ergebnismenge erzeugt, kann der Trigger nicht gestartet werden und der Anmeldeversuch, der den Trigger ausgelöst hat, wird verweigert.

Mehrere Trigger

SQL Server ermöglicht Ihnen, mehrere Trigger für jedes DML-, DDL- oder LOGON-Ereignis zu erstellen. Wenn zum Beispiel CREATE TRIGGER FOR UPDATE für eine Tabelle ausgeführt wird, die bereits einen UPDATE-Trigger hat, wird ein zusätzlicher Update-Trigger erstellt. In früheren Versionen von SQL Server ist für jede Tabelle nur ein Trigger für jedes INSERT-, UPDATE- oder DELETE-Datenänderungsereignis zulässig.

Rekursive Trigger

SQL Server unterstützt auch den rekursiven Aufruf von Triggern, wenn die Einstellung RECURSIVE_TRIGGERS mit ALTER DATABASE aktiviert wurde.

Rekursive Trigger ermöglichen die folgenden Arten der Rekursion:

  • Indirekte Rekursion

    Bei der indirekten Rekursion aktualisiert eine Anwendung die Tabelle T1. Diese feuert den Trigger TR1 ab, der die Tabelle T2 aktualisiert. Trigger T2 feuert dann und aktualisiert Tabelle T1.

  • Direkte Rekursion

    Bei der direkten Rekursion aktualisiert die Anwendung Tabelle T1. Dadurch wird der Trigger TR1 ausgelöst, der die Tabelle T1 aktualisiert. Da Tabelle T1 aktualisiert wurde, wird Trigger TR1 erneut ausgelöst usw.

Das folgende Beispiel verwendet sowohl die indirekte als auch die direkte Trigger-Rekursion Angenommen, es sind zwei Update-Trigger, TR1 und TR2, für Tabelle T1 definiert. Trigger TR1 aktualisiert die Tabelle T1 rekursiv. Eine UPDATE-Anweisung führt jeden TR1 und TR2 einmal aus. Zusätzlich löst der Start von TR1 die Ausführung von TR1 (rekursiv) und TR2 aus. 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 Einstellung RECURSIVE_TRIGGERS mit ALTER DATABASE aktiviert wurde. Es gibt keine definierte Reihenfolge, in der mehrere für ein bestimmtes Ereignis definierte Trigger ausgeführt werden. Jeder Trigger sollte in sich abgeschlossen sein.

Das Deaktivieren der Einstellung RECURSIVE_TRIGGERS verhindert nur direkte Rekursionen. Um auch indirekte Rekursionen zu deaktivieren, setzen Sie die Serveroption für verschachtelte Trigger mit sp_configure auf 0.

Wenn einer der Trigger eine ROLLBACK TRANSACTION ausführt, werden unabhängig von der Verschachtelungsebene keine weiteren Trigger ausgeführt.

Verschachtelte Trigger

Sie können Trigger auf maximal 32 Ebenen verschachteln. Wenn ein Trigger eine Tabelle ändert, auf der ein anderer Trigger steht, wird der zweite Trigger aktiviert und kann dann einen dritten Trigger aufrufen, und so weiter. Wenn ein Trigger in der Kette eine Endlosschleife auslöst, wird die Verschachtelungsebene überschritten und der Trigger wird abgebrochen. Wenn ein Transact-SQL-Trigger verwalteten Code aufruft, indem er auf eine CLR-Routine, einen Typ oder ein Aggregat verweist, zählt dieser Verweis als eine Ebene bei der 32-stufigen Verschachtelungsgrenze. Methoden, die innerhalb von verwaltetem Code aufgerufen werden, zählen nicht für diese Grenze.

Um verschachtelte Trigger zu deaktivieren, setzen Sie die Option nested triggers von sp_configure auf 0 (off). Die Standardkonfiguration unterstützt verschachtelte Trigger. Wenn verschachtelte Trigger deaktiviert sind, sind auch rekursive Trigger deaktiviert, trotz der Einstellung RECURSIVE_TRIGGERS, die mit ALTER DATABASE festgelegt wird.

Der erste AFTER-Trigger, der innerhalb eines INSTEAD OF-Triggers verschachtelt ist, wird auch dann ausgelöst, wenn die Serverkonfigurationsoption für verschachtelte Trigger auf 0 gesetzt ist. Aber unter dieser Einstellung werden die späteren AFTER-Trigger nicht ausgelöst. Überprüfen Sie Ihre Anwendungen auf verschachtelte Trigger, um festzustellen, ob die Anwendungen Ihren Geschäftsregeln folgen, wenn die Serverkonfigurationsoption für verschachtelte Trigger auf 0 gesetzt ist. Wenn nicht, nehmen Sie die entsprechenden Änderungen vor.

Aufgeschobene Namensauflösung

SQL Server ermöglicht es Transact-SQL gespeicherten Prozeduren, Triggern und Batches, sich auf Tabellen zu beziehen, die zur Kompilierungszeit nicht existieren. Diese Fähigkeit wird als verzögerte Namensauflösung bezeichnet.

Berechtigungen

Um einen DML-Trigger zu erstellen, ist die ALTER-Berechtigung für die Tabelle oder den View erforderlich, auf der/dem der Trigger erstellt wird.

Um einen DDL-Trigger mit Serverumfang (ON ALL SERVER) oder einen Anmeldetrigger zu erstellen, ist die CONTROL SERVER-Berechtigung auf dem Server erforderlich. Um einen DDL-Trigger mit Datenbankbereich (ON DATABASE) zu erstellen, ist die Berechtigung ALTER ANY DATABASE DDL TRIGGER in der aktuellen Datenbank erforderlich.

Beispiele

A. Verwenden eines DML-Triggers mit einer Erinnerungsmeldung

Der folgende DML-Trigger gibt eine Meldung an den Client aus, wenn jemand versucht, Daten in der Tabelle Customer in der AdventureWorks2012-Datenbank hinzuzufügen oder zu ändern.

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

Das folgende Beispiel sendet eine E-Mail-Nachricht an eine bestimmte Person (MaryM), wenn sich die Customer-Tabelle ändert.

CREATE TRIGGER reminder2 ON Sales.Customer AFTER INSERT, UPDATE, DELETE AS EXEC msdb.dbo.sp_send_dbmail @profile_name = 'AdventureWorks2012 Administrator', @recipients = '[email protected]', @body = 'Don''t forget to print a report for the sales force.', @subject = 'Reminder'; GO 

C. Verwenden eines DML AFTER-Triggers, um eine Geschäftsregel zwischen den Tabellen “PurchaseOrderHeader” und “Vendor” durchzusetzen

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

Das folgende Beispiel erstellt einen DML-Trigger in der AdventureWorks2012-Datenbank. Dieser Trigger überprüft, ob die Bonität des Lieferanten gut ist (nicht 5), wenn versucht wird, eine neue Bestellung in die Tabelle PurchaseOrderHeader einzufügen. Um die Bonität des Lieferanten zu erhalten, muss die Tabelle Vendor referenziert werden. Wenn die Bonität zu niedrig ist, erscheint eine Meldung und die Einfügung erfolgt nicht.

-- 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 * FROM Purchasing.PurchaseOrderHeader AS p JOIN inserted AS i ON p.PurchaseOrderID = i.PurchaseOrderID JOIN Purchasing.Vendor AS v ON v.BusinessEntityID = p.VendorID WHERE v.CreditRating = 5 ) BEGIN RAISERROR ('A vendor''s credit rating is too low to accept new purchase orders.', 16, 1); ROLLBACK TRANSACTION; RETURN END; GO -- This statement attempts to insert a row into the PurchaseOrderHeader table -- for a vendor that has a below average credit rating. -- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back. INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID, VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight) VALUES ( 2 ,3 ,261 ,1652 ,4 ,GETDATE() ,GETDATE() ,44594.55 ,3567.564 ,1114.8638 ); GO 

D. Verwendung eines datenbankübergreifenden DDL-Triggers

Im folgenden Beispiel wird ein DDL-Trigger verwendet, um zu verhindern, dass ein beliebiges Synonym in 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. Verwendung eines server-scoped DDL-Triggers

Das folgende Beispiel verwendet einen DDL-Trigger, um eine Meldung zu drucken, wenn ein CREATE DATABASE-Ereignis auf der aktuellen Serverinstanz eintritt, und verwendet die Funktion EVENTDATA, um den Text der entsprechenden Transact-SQL-Anweisung abzurufen. Weitere Beispiele für die Verwendung von EVENTDATA in DDL-Triggern finden Sie unter Verwenden der Funktion EVENTDATA.

Gilt für: SQL Server 2008 und höher.

CREATE TRIGGER ddl_trig_database ON ALL SERVER FOR CREATE_DATABASE AS PRINT 'Database Created.' SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)','nvarchar(max)') GO DROP TRIGGER ddl_trig_database ON ALL SERVER; GO 

F. Verwenden eines Anmeldetriggers

Das folgende Beispiel für einen Anmeldetrigger verweigert den Versuch, sich bei SQL Server als Mitglied der Anmeldung login_test anzumelden, wenn bereits drei Benutzersitzungen unter dieser Anmeldung laufen.

Gilt für: SQL Server 2008 und höher.

USE master; GO CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE, CHECK_EXPIRATION = ON; GO GRANT VIEW SERVER STATE TO login_test; GO CREATE TRIGGER connection_limit_trigger ON ALL SERVER WITH EXECUTE AS 'login_test' FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'login_test' AND (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = 'login_test') > 3 ROLLBACK; END; 

G. Anzeigen der Ereignisse, die das Auslösen eines Triggers verursachen

Das folgende Beispiel fragt die Katalogansichten sys.triggers und sys.trigger_events ab, um zu ermitteln, welche Ereignisse der Sprache Transact-SQL das Auslösen des Triggers safety verursachen. Der Trigger, safety, wird im obigen Beispiel ‘D’ 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 

See Also

ALTER TABLE (Transact-SQL)
ALTER TRIGGER (Transact-SQL)
COLUMNS_UPDATED (Transact-SQL)
CREATE TABLE (Transact-SQL)
DROP TRIGGER (Transact-SQL)
ENABLE TRIGGER (Transact-SQL)
DISABLE TRIGGER (Transact-SQL)
TRIGGER_NESTLEVEL (Transact-SQL)
EVENTDATA (Transact-SQL)
sys.dm_sql_referenced_entities (Transact-SQL)
sys.dm_sql_referencing_entities (Transact-SQL)
sys.sql_expression_dependencies (Transact-SQL)
sp_help (Transact-SQL)
sp_helptrigger (Transact-SQL)
sp_helptext (Transact-SQL)
sp_rename (Transact-SQL)
sp_settriggerorder (Transact-SQL)
UPDATE() (Transact-SQL)
Informationen über DML-Trigger erhalten
Informationen über DDL-Trigger erhalten
sys.triggers (Transact-SQL)
sys.trigger_events (Transact-SQL)
sys.sql_modules (Transact-SQL)
sys.assembly_modules (Transact-SQL)
sys.server_triggers (Transact-SQL)
sys.server_trigger_events (Transact-SQL)
sys.server_sql_modules (Transact-SQL)
sys.server_assembly_modules (Transact-SQL)

Schreib einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.