Frank Kalis

SELECT 
    CASE 
        WHEN OBJECTPROPERTY(OBJECT_ID('dbo.BookSource'), 'HasAfterTrigger') + OBJECTPROPERTY(OBJECT_ID('dbo.BookSource'), 'HasInsteadOfTrigger') > 0 
        THEN 'Trigger vorhanden' 
        ELSE 'Kein Trigger vorhanden' 
END 
GO 
IF EXISTS (SELECT 1 
        FROM 
            sys.triggers T 
        WHERE 
            T.parent_id = OBJECT_ID ('dbo.BookSource'))
    SELECT 'Trigger vorhanden' 
ELSE 
    SELECT 'Kein Trigger vorhanden'

SELECT
    T.[name] AS Trigger_Name,
    OBJECT_NAME(T.parent_id) AS Table_Name,
    TE.type_desc,
    T.create_date
FROM
    sys.triggers T
    JOIN
    sys.trigger_events TE ON T.[object_id] = TE.[object_id];

Typisches Einsatzgebiet ist die Implementierung einer eigenen Protokollierfunktionalität, die Änderungen an Tabellen mitschneidet. Die Arbeitsweise ist stets die gleiche, nur die Tabelle ist immer eine andere.

Ganze Geschichte »

Tips und Tricks, die die Performance von Trigger erhöhen können:

Die Zeit, die ein Trigger zur Ausführung benötigt, ist eine Funktion der Anzahl der innerhalb des Triggers referenzierten Tabellen und der Anzahl der vom Trigger Code betroffenen Zeilen. Daher sollte man stets bestrebt sein, die Anzahl der im Trigger involvierten Tabellen und die Anzahl der betroffenen Zeilen zu minimieren.

Zusätzlich sollte der Code eines Triggers auf ein Minimum reduziert werden, um Overhead zu vermeiden. Dies ist wichtig, da Trigger typischerweise durch INSERT's, UPDATE's und DELETE's ausgelöst werden, die in OLTP Applikation häufig vorkommen. Je mehr Code in einem Trigger ausgeführt werden muß, umso langsamer ist jedes INSERT, UPDATE und DELETE, das stattfindet.

*****

Falls der Trigger eine WHERE Klausel beinhaltet, sollte man nicht vergessen, einen geeigneten Index zu erstellen, der verwendet werden kann. WHERE Klauseln, die sich in Triggern verstecken, werden leicht übersehen, aber wie jedes andere SELECT Statement können auch sie signifikant durch einen geeigneten Index beschleunigt werden.
Ein Weg um dies zu testen ist, den Trigger Code im Query Analyzer laufen zu lassen und den daraus resultierenden Ausführungsplan zu untersuchen. Dadurch kann man sehr schnell feststellen, ob man einen geeigneten Index hinzufügen muß oder nicht.

*****

Hat man ein INSERT, UPDATE oder DELETE Statement daß länger zu benötigen scheint als angemessen, sollte man überprüfen, ob ein Trigger auf der beteiligten Tabelle definiert ist. Das Performance Problem, das man beobachtet, könnte sehr wohl auf den Trigger zurückzuführen zu sein, nicht auf das DML Statement selber.
Man sollte nicht vergessen, Trigger Code genauso zu optimieren wie man es mit jedem anderen Code auch macht. Aufgrund der Tatsache, daß Trigger Code im Hintergrund läuft, scheinen viele Leute zu vergessen, daß der Code existiert, und potentiell für Performance Probleme verantwortlich sein kann.
Man kann Profiler und Query Analyzer verwenden, um herauszufinden, wie die Trigger in einer Datenbank arbeiten.

*****

Verwenden Sie keinen Trigger, um referenzielle Integrität sicherzustellen, wenn Sie die Option haben, auf die in SQL Server eingebauten Funktionen zur Sicherstellung referenzieller Integrität zurückzugreifen. Die Verwendung der hoch optimierten eingebauten referenziellen Integrität ist deutlich schneller als die Verwendung eines Triggers und interpretiertem Code, um die gleiche Aufgabe zu erfüllen.

*****

Wenn Sie die Wahl haben zwischen einem Trigger und einer CHECK Constraint, um Regeln oder Standards in der Datenbank sicherzustellen, sollte man sich generell für die CHECK Constraint entscheiden, da sie schneller sind als Trigger, wenn sie die gleiche Aufgabe erfüllen sollen

*****

Man sollte es vermeiden, einen Trigger zurückrollen zu müssen aufgrund des verursachten Overheads. Anstelle den Trigger ein Problem finden zu lassen und evtl. eine Transaktion zurückrollen zu müssen, sollte man den Fehler vorher versuchen abzufangen, falls das aufgrund des involvierten Codes möglich ist. Einen Fehler abzufangen (bevor der Trigger ausgelöst wird), verbraucht wesentlich weniger Server Resourcen als den Trigger zurückrollen zu müssen.

*****

Manchmal erscheint es aus Performancegrunden angebracht, denormalisierten Daten zu halten. Beispielsweise mag man aggregierte ( so z.B. kumulierte Daten) in einer Tabelle halten, da es einfach zu zeitaufwendig ist, sie on-the-fly innerhalb des SELECT Statements zu generieren. Ein Weg, diese denormalisierten Daten zu pflegen, ist die Verwendung von Triggern. Beispielsweise könnte ein Trigger immer dann ausgelöst werden, wenn ein neuer Verkauf zur Verkaufstabelle hinzugefügt wird und den Gesamtwert dieses Verkaufs zu einer Gesamtverkaufstabelle hinzufügen.

*****

Der Code, der in einem UPDATE Trigger enthalten ist, wird jedesmal ausgeführt, wenn seine zugrundeliegende Tabelle upgedatet wird. In den meisten UPDATE Triggern, betrifft der Code des Triggers nur einige wenige Spalten, nicht alle. Darum ist es nicht sinnvoll (und eine Verschwendung von SQL Server Resourcen) den gesamte Code des Triggers auszuführen, auch wenn die Spalten, an denen man ursprünglich interessiert war, überhaupt nicht aktualisiert worden sind. Mit anderen Worten, auch wenn eine Spalte aktualisiert wird, an der man nicht interessiert ist, wird der UPDATE Trigger ausgelöst und der Code ausgeführt.
Um nun die unnötige Ausführung von Code in einem UPDATE Trigger zu vermeiden, kann man eine der beiden folgenden Funktionen einsetzen: UPDATE() (verfügbar in SQL Server 2000) und COLUMNS_UPDATED() (verfügbar in SQL Server 7.0 und 2000).
Jede dieser beiden Funktionen kann verwenden werden, um zu testen, ob eine bestimmte Spalte sich verändert hat oder nicht. Daher kann man nun Code in seinem Trigger schreiben, der genau dies überprüft und falls sich diese Spalte nicht verändert hat, der Code halt nicht ausgeführt wird. Dies reduziert die Arbeit, die der Trigger ausführen muß und verbessert die allgemeine Performance der Datenbank.
Die UPDATE() Funktion wird verwendet, um die Veränderung einer einzelnen Spalte zu einer Zeit zu überprüfen. Die COLUMNS_UPDATED Funktion kann verwendet werden, um gleichzeitig mehrere Spalten zu überprüfen.

*****

Falls Sie kaskadierende referentielle Integrität (zum Beispiel kaskadierende DELETEs) in SQL Server 2000 Datenbanken, verwenden Sie kaskadierende referentielle Integritäts Constraints anstelle von Triggern, die die kaskadierenden DELETEs ausführen, da Constraints wesentlich effizienter sind und dadurch die Performance verbessern können. Falls man ältere (7.0 oder früher) Applikationen hat, die man auf SQL Server 2000 portiert hat und die Trigger benutzen, um kaskadierende DELETEs, sollte man in Erwägung ziehen, die Trigger, falls möglich zu entfernen und kaskadierende referentielle Integrität zu verwenden.

*****

Während INSTEAD OF trigger technisch identisch mit AFTER Triggern sind, liegt der Hauptgrund für die Verwendung von INSEAD OF Trigger darin, daß man mit ihnen bestimmte View Typen aktualisieren kann. Was bedeutet dies nun im Hinblick auf Performance? Unter der Annahme, daß die meisten, der von Ihnen geschriebenen Trigger, nur selten Transaktionen zurückrollen, kann man getrost weiterhin AFTER Trigger verwenden. Der Overhead eines INSTEAD OF Triggers ist größer als der eines AFTER Trigers. Ist es hingegen an der Regel, daß ROLLBACKs durchgeführt werden (mehr als in 50% aller Fälle), dann kann ein INSTEAD OF Trigger die bessere Alternative sein, da das ROLLBACK eines INSTEAD OF Triggers weniger Overhead verursacht als das eines AFTER Triggers. Also sollte man die meiste Zeit konventionelle AFTER Trigger verwenden, und sich die INSTEAD OF Trigger sparen, um damit Views upzudaten.

*****

SQL Server 2000 ermöglicht in gewissem Maße, Kontrolle über die Reihenfolge in der Trigger auszuüben. In gewissem Maße heißt, daß man angeben kann, welcher Trigger als Erster und welcher als Letzter ausgeführt wird. hat man jedoch mehr als zwei Trigger für eine Tabelle definiert, hat man keine Kontrolle über die Reihenfolge, in der die restlichen ausgeführt wird.
So, wie nun kann die Selektion der Reihenfolge, in der die Trigger ausgeführt werden, dabei helfen, die Performance einer Applikation zu verbessern? Um Trigger Performance zu optimieren, sollte man denjenigen Trigger, bei dem ein ROLLBACK am wahrscheinlichsten (aus welchem Grund auch immer), als ersten auszuführenden Trigger spezifizieren. Auf diese Weise ist nur dieser eine Trigger betroffen, wenn ein ROLLBACK durchgeführt werden muß.
Mal angenommen, man hat drei Trigger auf einer Tabelle definiert, aber anstelle dem am wahrscheinlichsten als ersten zu definieren, sei dieser als letzter definiert. Wird in diesem Szenario nun ein ROLLBACK ausgelöst, dann müssen alle drei Trigger. Wäre dieser Trigger aber als Erster definiert worden, hätte nur dieser Trigger zurückgerollt werden müssen. Verringert man die Anzahl der Trigger, die zurückgerollt werden müssen, reduziert man SQL Server's Overhead und verbessert die Performance.

Gleich vorwegschicken möchte ich, daß dieser Beitrag nicht zur Nachahmung oder zum Einsatz in Produktivsystemen gedacht ist. Vielmehr eher als reine Spielerei mit dem, was möglich ist.

Also, kann die Ausführung eines Triggers umgangen werden? Einfache Antwort: Ja! Und zwar auf mehreren Wegen, von denen ich hier nur einen anführen will.

Die Ausgangssituation:

CREATE TABLE dbo.test_trigger
(
	c1 INT
	, c2 CHAR(10)
)
GO
CREATE TRIGGER fool_me ON dbo.test_trigger FOR INSERT
AS
	BEGIN
		UPDATE dbo.test_trigger SET c2 = 'Hallo Welt'
	END
	GO

Dieser Trigger soll also bei jedem INSERT den Wert 'Hallo Welt' in die Spalte c2 schreiben. Das das funktioniert, kann man einfach feststellen:

INSERT INTO dbo.test_trigger (c1) VALUES(1)
INSERT INTO dbo.test_trigger (c1, c2) VALUES(1, 'wtf')
SELECT * FROM dbo.test_trigger

c1          c2         
----------- ---------- 
1           Hallo Welt
1           Hallo Welt

So, egal,ob man nun explizit einen Wert in die Spalte c2 eintragen will oder nicht, der Wert wird durch den Trigger überschrieben.

Die Manipulation:

Und zwar nutzen wir die Spalte context_info in der master.dbo.sysprocesses Tabelle. Diese kann durch die Einstellung SET CONTEXT_INFO zusätzliche 128 Bytes pro Session oder Connection speichern. Zunächst ändern wir mal unseren Trigger ab.

ALTER TRIGGER fool_me ON dbo.test_trigger FOR INSERT AS
IF NOT (SELECT context_info FROM master.dbo.sysprocesses
WHERE spid = @@SPID) = 0xA
	BEGIN
		UPDATE dbo.test_trigger SET c2 = 'Hallo Welt'
	END
GO

So, nun können wir SET CONTEXT_INFO verwenden:

SET CONTEXT_INFO 0xA
INSERT INTO dbo.test_trigger (c1, c2) VALUES(2, 'q.e.d')
SET CONTEXT_INFO 0

Das Ergebnis:

SELECT * FROM dbo.test_trigger

c1          c2         
----------- ---------- 
1           Hallo Welt
1           Hallo Welt
2           q.e.d     

Nochmals der Hinweis: Dies ist kein Skript, was in Produktionssystemen verwendet werden sollte. Die Manipulation von SET CONTEXT_INFO kann zu Nebeneffekten führen bei anderen DB Objekten, die ebenfalls auf diese Einstellung zugreifen.

Danke an Jonathan van Houtte!

Die nachfolgenden Informationen gelten nur für AFTER Trigger, nicht für INSTEAD OF Trigger. Grundsätzlich kann man (theoretisch) beliebig viele AFTER Trigger pro Tabelle definieren. Die Anzahl ist hierbei nur durch die maximalen Objekte pro Datenbank von 2.147.483.647 beschränkt. Wenn es nun auf eine gewisse Abfolge ankommt, in welcher die Trigger abgefeuert werden, so kann man auf diese einen gewissen Einfluß ausüben durch die Systemprozedur sp_settriggerorder. Diese hat folgende Syntax:

sp_settriggerorder[@triggername = ] 'triggername' 
    , [@order = ] 'value' 
    , [@stmttype = ] 'statement_type' 

Ein beispielhafter Aufruf sieht dann folgendermaßen aus:

Den Einfluß, den man ausüben kann beschränkt sich aber darauf, festzulegen, welcher Trigger als Erster und welcher Trigger als Letzter abgefeuert wird. Dies erfolgt durch Angabe von

@order='first'

oder

@order='last'

Dazwischen ist die Reihenfolge der Ausführung undefiniert.

@order='none'

Weitere Erklärungen stehen in BOL.

Zuerst stellt sich aber die Frage, warum man so etwas machen möchte. Zu den wohl am häufigsten genannten Gründe zählt:

Ganze Geschichte »

Keine Chance!

Jeder Fehler innerhalb des Triggers beendet diesen, die zugrundeliegende Transaktion und den Batch.