CREATE TRIGGER (Transact-SQL)

Gilt für: SQL Server Azure SQL-Datenbank Azure 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-, UPDATE- oder DELETE-Anweisungen für eine Tabelle oder Sicht. 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, Datendefinitionssprache) ausgeführt. Diese Ereignisse entsprechen im Wesentlichen den Transact-SQL-Anweisungen CREATE, ALTER und DROP 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. 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.

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

-- SQL Server Syntax  
-- Trigger on an INSERT, UPDATE, or DELETE statement to a 
-- table (DML Trigger on memory-optimized tables)  
  
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 on a CREATE, ALTER, DROP, GRANT, DENY, 
-- REVOKE or UPDATE statement (DDL Trigger)  
  
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 on a LOGON event (Logon Trigger)  
  
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-Datenbank-Syntax

-- Azure SQL Database Syntax   
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (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 ]  
  

-- Azure SQL Database Syntax  
-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, 
-- REVOKE, or UPDATE STATISTICS statement (DDL Trigger)   
  
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

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

Ä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, und trigger_name darf nicht mit # oder ## beginnen.

table | view
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 mit einem INSTEAD OF-Trigger auf eine Sicht verweisen. Sie können DML-Trigger nicht 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 jedes Mal ausgelöst, wenn in der aktuellen Datenbank event_type oder event_group auftritt.

ALL SERVER
Gilt für: SQL Server 2008 (10.0.x) und später

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
Gilt für: SQL Server 2008 (10.0.x) und später

Verbirgt den Text der CREATE TRIGGER-Anweisung. Durch das Verwenden von WITH ENCRYPTION kann verhindert werden, dass der Trigger als Teil der SQL Server-Replikation veröffentlicht wird. WITH ENCRYPTION kann nicht für CLR-Trigger angegeben werden.

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.

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

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

NATIVE_COMPILATION
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 | AFTER
FOR und AFTER geben an, dass der DML-Trigger nur dann ausgelöst wird, nachdem alle Vorgänge, die in der den Trigger auslösenden SQL-Anweisung festgelegt sind, 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 keine AFTER-Trigger für Ansichten definieren.

INSTEAD OF
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 nicht INSTEAD OF für DDL- oder LOGON-Trigger angeben.

Sie können maximal nur einen INSTEAD OF-Trigger pro INSERT-, UPDATE- oder DELETE-Anweisung für eine Tabelle oder Sicht definieren. Sie können auch Sichten auf Sichten definieren, wobei jede Sicht über einen eigenen INSTEAD OF-Trigger verfügt.

Sie können nicht INSTEAD OF-Trigger für aktualisierbare Sichten definieren, die WITH CHECK OPTION verwenden. Dies löst einen Fehler aus, falls ein INSTEAD OF-Trigger einer aktualisierbaren Sicht hinzugefügt wird, in der WITH CHECK OPTION angegeben ist. Sie entfernen diese Option mithilfe von 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 DELETE nicht für Tabellen mit einer referenziellen Beziehung untereinander verwenden, 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 Aktion CASCADE angegeben ist.

WITH APPEND
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 falls der AFTER-Trigger explizit angegeben ist. 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
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.

Nach dem Ausführen von CREATE TRIGGER dient event_group außerdem als Makro, das der sys.trigger_events-Katalogsicht die verarbeitbaren Ereignistypen hinzufügt.

NOT FOR REPLICATION
Gilt für: SQL Server 2008 (10.0.x) und später

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 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 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 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 unter Verwendung der Funktion EVENTDATA (Transact-SQL) Informationen zum auslösenden Ereignis auf. Weitere Informationen finden Sie unter Verwenden der EVENTDATA-Funktion.

SQL Server ermöglicht das Aktualisieren der Spalten text, ntext oder image mithilfe des INSTEAD OF-Triggers für Tabellen oder Sichten.

Bei Triggern in speicheroptimierten Tabellen ist auf der obersten Ebene nur ein ATOMIC-Block als sql_statement erlaubt. Das im ATOMIC-Block erlaubte T-SQL ist durch das in nativen Prozeduren zulässige T-SQL beschränkt.

< method_specifier >Gilt für: SQL Server 2008 (10.0.x) und später

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

Hinweise zu DML-Triggern

DML-Trigger werden häufig zum Erzwingen von Geschäftsregeln und Datenintegrität verwendet. 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.

Sie können den ersten und letzten für eine Tabelle auszuführenden AFTER-Trigger mit „sp_settriggerorder“ angeben. Sie können in einer Tabelle für jeden INSERT-, UPDATE- und DELETE-Vorgang nur einen einzigen ersten und letzten AFTER-Trigger angeben. Sind für eine Tabelle weitere AFTER-Trigger vorhanden, werden diese nach dem Zufallsprinzip ausgeführt.

Wenn eine ALTER TRIGGER-Anweisung den ersten oder letzten Trigger ändert, wird das erste oder letzte für den geänderten Trigger festgelegte Attribut gelöscht, und Sie müssen den Reihenfolgewert mit „sp_settriggerorder“ zurücksetzen.

Ein AFTER-Trigger wird nur dann ausgeführt, wenn die den Trigger auslösende SQL-Anweisung erfolgreich ausgeführt wurde. Diese erfolgreiche Ausführung umfasst alle referenziellen kaskadierenden Aktionen und Einschränkungsüberprüfungen, die mit dem aktualisierten oder gelöschten Objekt verknüpft sind. Ein AFTER-Trigger löst nicht rekursiv einen INSTEAD OF-Trigger für dieselbe Tabelle aus.

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 rekursiv 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. Beispiel: Ein Trigger ist als INSTEAD OF INSERT-Trigger für eine Tabelle definiert. Außerdem führt der Trigger eine INSERT-Anweisung für dieselbe Tabelle aus; die vom INSTEAD OF-Trigger gestartete INSERT-Anweisung ruft den Trigger nicht erneut auf. Die vom Trigger gestartete INSERT-Anweisung startet das Ausführen der Einschränkungsaktionen und löst alle für die Tabelle definierten AFTER INSERT-Trigger aus.

Wenn 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 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).

Beispiel: Ein Trigger ist als INSTEAD OF UPDATE-Trigger für eine Sicht definiert. Außerdem führt der Trigger eine UPDATE-Anweisung aus, die auf dieselbe Sicht verweist; die vom INSTEAD OF-Trigger gestartete UPDATE-Anweisung ruft den Trigger nicht erneut auf. Die von dem Trigger gestartete 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, ob UPDATE- oder INSERT-Aktionen für mehrere Spalten ausgeführt wurden. Diese Funktion gibt ein Bitmuster zurück, das angibt, 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 oder Anweisungen zurückgeben, die Variablenzuweisungen durchführen. Ein Trigger, der entweder SELECT-Anweisungen enthält, die Ergebnisse an den Benutzer zurückgeben, oder Anweisungen, die Variablen zuweisen, erfordert eine besondere Behandlung. Sie müssten die zurückgegebenen Ergebnisse in alle Anwendungsprogramme schreiben, 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 mit einer DELETE-Anweisung vergleichbar ist, löst sie keine Trigger aus, da die Löschung einzelner Zeilen nicht protokolliert wird. Allerdings müssen nur die Benutzer, die die Berechtigung zur Ausführung einer TRUNCATE TABLE-Anweisung besitzen, beachten, dass ein DELETE-Trigger durch eine TRUNCATE TABLE-Anweisung 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
• 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 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

Optimieren von DML-Triggern

Trigger funktionieren in Transaktionen (impliziert oder anderweitig), und während sie offen sind, sperren sie Ressourcen. Die Sperre wird beibehalten, bis die Transaktion bestätigt (mit COMMIT) oder abgelehnt wurde (mit 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 zu DDL-Triggern

DDL-Trigger starten, ebenso wie Standardtrigger, gespeicherte Prozeduren als Reaktion auf ein Ereignis. 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. Stattdessen führen sie sie primär als Reaktion auf DDL-Anweisungen (Data Definition Language, Datendefinitionssprache) aus. Zu den Anweisungstypen zählen CREATE, ALTER, DROP, GRANT, DENY, REVOKE und UPDATE STATISTICS. Bestimmte gespeicherte Systemprozeduren, die DDL-ähnliche Vorgänge ausführen, können ebenfalls DDL-Trigger auslösen.

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. Aus diesem Grund können Sie Funktionen wie OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY und OBJECTPROPERTYEX nicht verwenden, um Metadaten aus DDL-Triggern abzufragen. Verwenden Sie stattdessen die Katalogsichten. Weitere Informationen finden Sie unter Abrufen von Informationen zu DDL-Triggern.

Logon-Trigger

Logon-Trigger führen gespeicherte Prozeduren als Antwort 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. Aus diesem Grund werden alle Meldungen, die aus dem Trigger stammen und normalerweise den Benutzer erreichen (z.B. Fehlermeldungen und Meldungen aus der PRINT-Anweisung) zum SQL Server-Fehlerprotokoll umgeleitet. Weitere Informationen finden Sie unter Logon-Trigger.

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 Logon-Triggers

Ein Logon-Trigger kann effektiv erfolgreiche Verbindungen zu Datenbank-Engine für alle Benutzer verhindern, einschließlich Elementen der festen Serverrolle sysadmin . Wenn ein LOGON-Trigger Verbindungen verhindert, können die Mitglieder der festen Serverrolle sysadmin über die dedizierte Administratorverbindung eine Verbindung herstellen oder durch Starten des Datenbank-Engine s im minimalen Konfigurationsmodus (-f). Weitere Informationen finden Sie unter Startoptionen für den Datenbank-Engine-Dienst.

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

SQL Server ermöglicht Ihnen 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.

Rekursive Trigger

SQL Server unterstützt 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. Trigger TR2 löst dann Tabelle T1 aus und aktualisiert sie.

• Direkte Rekursion

  Bei der direkten Rekursion aktualisiert die Anwendung Tabelle T1. 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 Updatetrigger, 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 der Start 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.

Durch Deaktivierung der RECURSIVE_TRIGGERS-Einstellung werden nur die direkten Rekursionen verhindert. Um die indirekte Rekursion zu deaktivieren, legen Sie die Serveroption Geschachtelte Trigger 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

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 "Geschachtelte Trigger" von "sp_configure" auf 0 (deaktiviert) fest. Die Standardkonfiguration unterstützt geschachtelte Trigger. Wenn geschachtelte Trigger deaktiviert wurden, sind rekursive Trigger ebenfalls deaktiviert, unabhängig von der durch ALTER DATABASE festgelegten RECURSIVE_TRIGGERS-Einstellung.

Der erste AFTER-Trigger, der in einem INSTEAD OF-Trigger geschachtelt ist, wird auch dann ausgelöst, wenn die Serverkonfigurationsoption geschachtelte Trigger 0 (null) ist. Bei dieser Einstellung werden jedoch nachfolgende AFTER-Trigger 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.

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.

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 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 E-Mail-Erinnerungsnachricht

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  
   EXEC 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 sich CHECK-Einschränkungen nur auf Spalten beziehen, für die die Einschränkung auf Spalten- oder Tabellenebene definiert wurden, müssen Sie tabellenübergreifende 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   
           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 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: 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 Meldung auszugeben, wenn ein CREATE DATABASE-Ereignis auf der aktuellen Serverinstanz eintritt. Mithilfe der EVENTDATA-Funktion wird der Text der entsprechenden Transact-SQL-Anweisung abgerufen. Weitere Beispiele für die Verwendung von EVENTDATA in DDL-Triggern finden Sie unter Verwenden der EVENTDATA-Funktion.

Gilt für: SQL Server 2008 (10.0.x) und später

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

Gilt für: SQL Server 2008 (10.0.x) und später

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 einen Trigger auslösen

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 Trigger safety wird in Beispiel „D“ erstellt, wie oben dargestellt.

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  

Weitere Informationen

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)
Abrufen von Informationen zu DML-Triggern
Abrufen von Informationen zu DDL-Triggern
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)