Frank Kalis

Herunterladen: Trigger.sql

Herunterladen von: Trigger.sql It's all in the manual, stupid - Trigger

Vor 3 Jahren haben wir für eines unserer Systeme Code implementiert, der auf diesem exzellenten Blogeintrag von SQL Server MVP Paul Nielsen basiert. Um genau zu sein, geht es hier um den Punkt 4 aus diesem Blog. Unser Code hat sich seit seiner Implementierung nicht mehr geändert; sollte nun aber im Zuge einer Erweiterung angepasst werden. Konkret sieht der Code in etwa so aus:

    BEGIN TRY
                …
            INSERT INTO dbo.TableName
                (SomeValue)
            SELECT
                @ISomeValue
            WHERE NOT EXISTS (SELECT 1
                        FROM
                            dbo.TableName C
                        WHERE
                            C.SomeValue = @ISomeValue);
                …            
        
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN;
        
        IF ERROR_NUMBER() <> 2601 --UQ violation
        BEGIN
        …
        END
    END CATCH

Die Stellen, die ich jetzt ausgelassen habe, spielen für die weitere Betrachtung keine Rolle und können getrost ausgelassen werden. Die Prozedur funktioniert einwandfrei und bis vor kurzem haben wir auch seit Jahren nicht mehr darüber nachgedacht. Dann jedoch meinte mein Kollege beim Code-Review meiner Änderung, dass wir zum Glück keinen Trigger auf der Zieltabelle haben weil dieser jedesmal ausgeführt werden würde. Ohne lange darüber nachzudenken habe ich geantwortet, dass eigentlich durch das NOT EXISTS() Konstrukt dies gerade nicht der Fall sein dürfte. Nun, offensichtlich lag ich falsch und hätte entweder vorher gründlich nachdenken oder diese weisen Worte berücksichtigen sollen:

Der Trigger wird tatsächlich jedesmal ausgelöst,auch wenn es offensichtlich nichts zu tun gibt und das INSERT Statement eigentlich gar nicht ausgeführt werden bräuchte. Selbstverständlich wird der Trigger jedesmal ausgeführt, bin ich versucht, im Nachhinein zu sagen. Jetzt, wo ich meine, sowohl den Blogeintrag von Paul verstanden zu haben als auch den entsprechenden Text in der Dokumentation.

Aber der Reihe nach anhand eines Beispieles:

SET NOCOUNT ON;
GO
IF OBJECT_ID('dbo.TestTable') IS NOT NULL 
    DROP TABLE dbo.TestTable;
GO
CREATE TABLE dbo.TestTable (
    pk int NOT NULL
           IDENTITY(1, 1)
           CONSTRAINT pk_whatever PRIMARY KEY,
    vc varchar(10) NOT NULL
                   CONSTRAINT vc_whatever UNIQUE);
GO

 

Zunächst erstellen wir eine einfache Tabelle mit 2 Spalten und 2 Einschränkungen. Wichtig ist die UNIQUE Einschränkung, da wir darüber wieder die Brücke schlagen zu Paul Nielsen.

CREATE TRIGGER dbo.TestTableTrigger ON dbo.TestTable
    FOR INSERT, UPDATE, DELETE
AS
PRINT 'Action performed';
GO

Jetzt kommt noch ein einfacher (AFTER) Trigger auf diese Tabelle. Die einzige Aktion innerhalb des Triggers ist die Ausgabe des PRINT Statements. Nichts weiter.

Jetzt kommen wir zu unseren Szenarien:

1.	Szenario: 
--First pass = INSERT
--Subsequent passes = no trigger execution
IF NOT EXISTS ( SELECT
                    *
                FROM
                    dbo.TestTable
                WHERE
                    vc = 'Value1' ) 
    INSERT  INTO dbo.TestTable
            SELECT
                'Value1';
GO

Dies ist der wohl klassische Ansatz für diese Art von Aufgabenstellungen. Wenn der Wert nicht in der Tabelle existiert, füge ihn hinzu. Bei der ersten Ausführung wird das INSERT ausgeführt und natürlich der Trigger ausgelöst. Alle nachfolgenden Ausführungen kommen nicht mehr zu diesem INSERT, da ja der Wert nun bereits existiert. Also wird bei den nachfolgenden Ausführungen auch der Trigger nicht mehr ausgelöst.

Das war auch unser erster Ansatz, der aber aus den bei Paul Nielsen erwähnten Gründen nicht gut genug war und dazu führte, diese Logik einzusetzen:

2.	Szenario
--First pass = INSERT
--Subsequent passes = trigger execution
INSERT  INTO dbo.TestTable
        SELECT
            'Value1'
        WHERE
            NOT EXISTS ( SELECT
                            *
                         FROM
                            dbo.TestTable
                         WHERE
                            vc = 'Value1' );

Wie man anhand des angehängten Skriptes leicht nachvollziehen kann, wird der Trigger tatsächlich jedesmal ausgelöst, auch wenn der Wert bereits existiert (Erklärungsversuch weiter unten).

Seit SQL Server 2008 kommt mit MERGE eine weitere Alternative in Spiel:

3.	Szenario: 
--First pass = INSERT
--Subsequent passes = trigger execution
MERGE dbo.TestTable AS target
    USING 
        (SELECT 'Value1') AS source (Vc)
    ON (target.vc = source.vc)
    WHEN NOT MATCHED 
        THEN	
	    INSERT
            (vc)
          VALUES
            ('Value1');

Aber auch hier kann man sehen, dass der Trigger stets ausgeführt wird. Auch dies steht in der Dokumentation, und zwar hier:

Für jeden Einfüge-, Update- oder Löschvorgang, der in der MERGE-Anweisung angegeben ist, löst SQL Server alle entsprechenden AFTER-Trigger aus, die in der Zieltabelle definiert sind, gewährleistet jedoch nicht, für welche Aktion Trigger zuerst oder zuletzt ausgelöst werden. Trigger, die für dieselbe Aktion definiert sind, halten sich an die von Ihnen angegebene Reihenfolge.

Abschliessend ein weiteres Szenario:

3.	Szenario
--First pass = INSERT
--Subsequent passes = Constraint checked before trigger execution => violation and no trigger execution
BEGIN TRY
    INSERT  INTO dbo.TestTable
            SELECT
                'Value1';
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() <> 2627
        --swallow the error silently
        PRINT ERROR_MESSAGE();
END CATCH
GO

Nachdem ich den Blogeintrag von Paul dann noch einmal sorgfältig durchgelesen habe, ist dies wohl die Logik, die wir von Anfang an hätten implementieren sollen. Und siehe da! Der Trigger wird ab der zweiten Ausführung NICHT mehr ausgelöst. Grund dafür ist, dass die Einschränkung gecheckt wird vor dem Trigger. Da dieser Check eine Violation angezeigt, braucht der Trigger nicht auch noch ausgelöst zu werden. Nachzulesen hier.

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.

Somit sind wir auf der Suche nach einer anderen Antwort kaum 3 Jahre später auf das gestossen, was Paul Nielsen (wahrscheinlich) ursprünglich im Sinn gehabt hat, als er seinen Artikel schriebt. Damit war zumindest ein Teil des Rätsels gelöst, aber die ursprüngliche Frage, warum unsere bisherige Logik einen Trigger jedesmal auslösen würde, war noch nicht beantwortet. Doch nun passierte das Wunder, dass auf einmal aneinandergereihte Buchstaben Sinn machen, wenn man sich nur einmal die Mühe macht, sie (mehrmals) sorgfältig zu lesen und/oder lange genug regungslos auf den Bildschirm zu starren. Die Erklärung für dieses Verhalten findet sich an gleicher Stelle in BOL etwas weiter oben:

{ [ 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. Es muss mindestens eine Option angegeben werden. Die Optionen können in beliebiger Kombination und Reihenfolge in der Triggerdefinition angegeben werden. Für INSTEAD OF-Trigger ist die Option DELETE nicht für Tabellen mit einer referenziellen Beziehung untereinander zulässig, 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 Option CASCADE angegeben ist.

Diese Stelle (Hervorhebung vom mir) liefert den Grund, warum der Trigger ausgelöst wird. Zur Verdeutlichung noch einmal unser Statement aus Szenario 2:

2.	Szenario
--First pass = INSERT
--Subsequent passes = trigger execution
INSERT  INTO dbo.TestTable
        SELECT
            'Value1'
        WHERE
            NOT EXISTS ( SELECT
                            *
                         FROM
                            dbo.TestTable
                         WHERE
                            vc = 'Value1' );

Der Schlüssel zum Verständnis liegt nun im SELECT Teil:

        SELECT
            'Value1'
        WHERE
            NOT EXISTS ( SELECT
                            *
                         FROM
                            dbo.TestTable
                         WHERE
                            vc = 'Value1' );

Auch wenn dieser Teil das NOT EXISTS enthält, um vermeidlich unnötige Arbeit zu vermeiden, so produziert das SELECT Statement IN JEDEM FALL ein Result Set. Ob dieses Result Set nun tatsächlich Zeilen enthält oder in der überwiegenden Mehrzahl von Ausführungen leer sein wird, spielt keine wirkliche Rolle. Allein die Tatsache, dass es dieses Result Set gibt, reicht, um den Trigger auszulösen. Andersrum ausgedrückt: Gäbe es dieses Result Set NICHT, würde der Trigger auch nicht auslösen. Scheint eigentlich ganz logisch zu sein, wenn man länger drüber nachdenkt und begriffen hat, was "Anweisungen zur Datenänderung" tatsächlich bedeutet.

Ein Performance-Vergleich der diversen Ansätze soll zwar nicht Gegenstand dieses Artikels sein, jedoch bietet ein Blick auf die Ausführungspläne weitere hilfreich Einsichten.

Als erstes der Plan zu Szenario 1 (ab der zweiten Ausführung). Wie man sehen kann, sieht man keine INSERT Operation und damit auch keine Trigger Ausführung.

Nun der Plan für unsere bisherige Logik. Wie man sieht, enthält jede Ausführung die "Clustered Index Insert" Operation. Damit wird also auch der Trigger jedes Mal ausgelöst.

Zu guter Letzt noch der Ausführungsplan zum MERGE Szenario. Auch hier kann man bei jeder Ausführung die "Clustered Index Merge" Operation beobachten.

Danke an Christoph Muthmann, den ich im Vorfeld angemailt habe und seine Meinung zu meiner "Theorie" zu hören und der sich nicht nur die Zeit genommen hat, meine konfuse Mail zu lesen, sondern auch das Ganze ausprobieren, das MERGE Szenario hinzuzufügen und die Screenshots der Ausführungspläne beizusteuern.