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 |
|
Wichtig |
---|
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:
|
|
|
Verbergen von DML-Triggerdefinitionen
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.
Optionen der SET-Anweisung
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.
Testen im Hinblick auf Änderungen an bestimmten Spalten
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.
Hinweis |
---|
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.
Beispiele
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
Verzögerte Namensauflösung
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.
Hinweis |
---|
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. |
Zurückgeben von Ergebnissen
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.
Wichtig |
---|
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. |