Programmieren von DML-Triggern

Nahezu jede Transact-SQL-Anweisung, die als Batch geschrieben werden kann, kann zum Erstellen eines DML-Triggers verwendet werden. Folgende Anweisungen sind hiervon ausgenommen:

ALTER DATABASE

CREATE DATABASE

DROP DATABASE

LOAD DATABASE

LOAD LOG

RECONFIGURE

RESTORE DATABASE

RESTORE LOG

 

Wichtiger HinweisWichtig

Die LOAD DATABASE- und LOAD LOG-Anweisungen sind in SQL Server 2008 nur aus Gründen der Abwärtskompatibilität vorhanden und werden in zukünftigen Versionen möglicherweise nicht mehr unterstützt.

Außerdem sind die folgenden Transact-SQL-Anweisungen im Textkörper eines DML-Triggers unzulässig, wenn dieser für eine Tabelle oder Sicht verwendet wird, die das Ziel der auslösenden Aktion ist:

CREATE INDEX

ALTER INDEX

DROP INDEX

DBCC DBREINDEX

ALTER PARTITION FUNCTION

DROP TABLE

ALTER TABLE, wenn die Anweisung für Folgendes verwendet wird:

  • Hinzufügen, Ändern oder Löschen von Spalten.

  • Wechseln von Partitionen.

  • Hinzufügen oder Löschen eines Primärschlüssels oder von eindeutigen Einschränkungen.

 

 

Um den Originaltext der CREATE TRIGGER-Anweisung in ein verborgenes Format zu konvertieren, verwenden Sie die WITH ENCRYPTION-Option. Die Ausgabe der Verbergung ist in keiner der Systemtabellen oder Sichten in SQL Server 2008 direkt sichtbar: Benutzer ohne Zugriff auf Systemtabellen, Systemsichten oder Datenbankdateien können den verborgenen Text nicht abrufen. Der Text ist für entsprechend privilegierte Benutzer mit direktem Zugriff auf Datenbankdateien jedoch verfügbar. Diese Benutzer können die Verbergung möglicherweise zurückentwickeln, um den Originaltext der Triggerdefinition zu erhalten.

Wenn eine ODBC-Anwendung die Verbindung mit SQL Server herstellt, legt der Server automatisch die folgenden Optionen für die Sitzung fest:

  • SET QUOTED_IDENTIFIER ON

  • SET TEXTSIZE 2147483647

  • SET ANSI_DEFAULTS ON

  • SET CURSOR_CLOSE_ON_COMMIT OFF

  • SET IMPLICIT_TRANSACTIONS OFF

Durch diese Einstellungen wird die Portabilität von ODBC-Anwendungen erhöht. Da DB-Library-basierte Anwendungen diese Optionen normalerweise nicht festlegen, sollten Trigger mit den zuvor aufgeführten SET-Optionen sowohl im aktivierten als auch im deaktivierten Status getestet werden. Auf diese Weise wird sichergestellt, dass die Trigger ordnungsgemäß funktionieren, und zwar unabhängig von den Optionen, die für eine bestimmte Verbindung bei Aufruf des Triggers festgelegt sind. Wenn ein Trigger eine bestimmte Einstellung für eine dieser Optionen erfordert, sollte am Anfang des Triggers eine SET-Anweisung ausgeführt werden. Die SET-Anweisung behält ihre Gültigkeit nur während der Ausführung des Triggers bei. Wenn der Trigger beendet ist, wird die ursprüngliche Einstellung wiederhergestellt.

Mithilfe der UPDATE()-Funktion können Sie ermitteln, ob sich eine INSERT- oder eine UPDATE-Anweisung auf eine bestimmte Spalte in der Tabelle ausgewirkt hat. Die Funktion gibt TRUE zurück, wenn der Spalte ein Wert zugewiesen wird.

HinweisHinweis

Da ein bestimmter Wert in einer Spalte nicht mit der DELETE-Anweisung gelöscht werden kann, gilt eine IF UPDATE()-Klausel nicht für die DELETE-Anweisung.

Sie können stattdessen auch die COLUMNS_UPDATED-Funktion verwenden, um zu überprüfen, welche Spalten in einer Tabelle mit einer INSERT- oder einer UPDATE-Anweisung aktualisiert wurden. Diese Funktion verwendet eine ganzzahlige Bitmaske, um die zu testenden Spalten anzugeben. Weitere Informationen finden Sie unter CREATE TRIGGER.

A. Verwenden einer IF UPDATE()-Klausel, um Datenänderungen zu testen

Im folgenden Beispiel wird der INSERT-Trigger my_trig für die my_table-Tabelle erstellt, und es wird getestet, ob die b-Spalte von INSERT-Versuchen betroffen war.

CREATE TABLE my_table*
(a int NULL, b int NULL)
GO

CREATE TRIGGER my_trig
ON my_table
FOR INSERT
AS
IF UPDATE(b)
   PRINT 'Column b Modified'
GO

B. Verwenden der COLUMNS UPDATED-Funktion, um Datenänderungen zu testen

Das folgende Beispiel führt zu einem ähnlichen Ergebnis unter Verwenden der COLUMNS_UPDATED()-Klausel:

CREATE TRIGGER my_trig2
ON my_table
FOR INSERT
AS
IF ( COLUMNS_UPDATED() & 2 = 2 )
   PRINT 'Column b Modified'
GO

DML-Trigger können auf Tabellen verweisen, die zum Zeitpunkt der Erstellung des Triggers nicht vorhanden sind. Dies wird als verzögerte Namensauflösung bezeichnet. Weitere Informationen zur verzögerten Namensauflösung finden Sie unter Verzögerte Namensauflösung und Kompilierung.

HinweisHinweis

Wird ein Objekt, auf das von einem DML-Trigger verwiesen wird, gelöscht oder umbenannt, wird ein Fehler zurückgegeben, sobald der Trigger ausgeführt wird. Wird jedoch ein Objekt, auf das in einem DML-Trigger verwiesen wird, durch ein Objekt mit demselben Namen ersetzt, wird der Trigger ausgeführt, ohne dass er erneut erstellt werden muss. Wenn z. B. der trig1-Trigger auf die test1-Tabelle verweist, test1 gelöscht und eine andere Tabelle mit dem Namen test1 erstellt wird, verweist trig1 nun auf die neue Tabelle.

Es empfiehlt sich nicht, dass ein DML-Trigger Ergebnisse zurückgibt. In diesem Fall wäre in allen Anwendungsprogrammen, in denen Änderungen an der Triggertabelle zulässig sind, eine besondere Verarbeitung der zurückgegebenen Ergebnisse erforderlich. Um zu vermeiden, dass ein DML-Trigger Ergebnisse zurückgibt, sollten Sie in die Definition des Triggers keine SELECT-Anweisungen oder Variablenzuweisungen aufnehmen. Wenn Variablenzuweisungen in einem Trigger erfolgen müssen, verwenden Sie eine SET NOCOUNT-Anweisung vor dem Trigger, um die Rückgabe von Resultsets zu verhindern.

Wichtiger HinweisWichtig

Die Möglichkeit, Resultsets aus Triggern zurückzugeben, wird in einer künftigen Version von SQL Server entfernt. Vermeiden Sie sie deshalb bei Neuentwicklungen, Resultsets aus Triggern zurückzugeben, und planen Sie die Änderung von Anwendungen, in denen dies derzeit verwendet wird. Damit Trigger keine Resultsets zurückgeben, legen Sie disallow results from triggers (Option) auf 1 fest. In einer zukünftigen Version von SQL Server wird die Standardeinstellung für die Option 1 sein.

Community-Beiträge

HINZUFÜGEN
Anzeigen: