TRIGGER

Was sind TRIGGER?

Ihr habt im Dojo nun schon oft gehört, dass SQL immer dann effektiv ist, wenn ihr auf Datensätzen operiert und dass SQL eine Sprache ist, die Mengenoperationen ermöglicht. Deshalb sind auch die Möglichkeiten zur Ablaufsteuerung in SQL im Vergleich zu anderen Programmiersprachen eher dünn gesät, einige davon habt ihr bereits an dieser Stelle kennengelernt. Doch wie sieht es mit Funktionsaufrufen aus? Wie könnt ihr reagieren, wenn eine Zeile in eine Tabelle eingefügt wurde? Die Antwort auf diese Fragestellungen lautet TRIGGER.

Wofür könnt ihr TRIGGER verwenden?

Die meisten von euch kennen TRIGGER vermutlich als Konstrukt, das immer dann greift, wenn ein Datensatz eingefügt, verändert oder gelöscht wird. Darüber hinaus gibt es auch TRIGGER, die immer dann greifen, wenn Objekte in Datenbanken erstellt, verändert oder gelöscht werden. Außerdem gibt es noch LOGIN-TRIGGER. Was heißt aber nun “greifen”? Oft wird hier – in Analogie zum Trigger als Abzug einer Schusswaffe – auch von “feuern” gesprochen. Was damit genau gemeint ist, dafür legen wir uns erstmal einen TRIGGER an. Zunächst benötigen wir dafür zwei Tabellen, eine auf der wir den Trigger definieren und eine, in die wir Daten schreiben, wenn der Trigger “feuert”. Für diese Tabellen legen wir uns ein eigenes Schema an:

CREATE SCHEMA Trig
GO

In dieses Schema kommen jetzt wie gesagt zwei Tabellen:

DROP TABLE IF EXISTS Trig.FirstNames
DROP TABLE IF EXISTS Trig.FullNames

CREATE TABLE Trig.FirstNames (
     FirstNameId INT IDENTITY(1,1)
    ,FirstName varchar(250) not null
)

CREATE TABLE Trig.FullNames (
     FullNameId INT IDENTITY(10,10)
    ,FirstNameId INT NULL
    ,LastNameId INT NULL
    ,FirstName varchar(250) NULL
    ,LastName varchar(250) NULL
)

GO 

Nun sind wir mit den Vorbereitungen soweit fertig, dass wir einen Trigger definieren können:

CREATE TRIGGER Trig.TestInsertTrigger 
ON Trig.FirstNames
AFTER INSERT AS 
BEGIN
    SET NOCOUNT ON;
    INSERT INTO Trig.FullNames 
        (FirstName, FirstNameId) 
    SELECT 
        i.FirstName, i.FirstNameId 
    FROM inserted i 
END

GO

Wenn ihr euch dieses Statement anseht, dann seht ihr, dass es aus vier Teilen besteht, die wir uns nun ansehen möchten:

• CREATE TRIGGER mit dem Namen des Triggers definiert, was für ein Objekt wir anlegen möchten und wie es heißen soll.
• ON definiert die Tabelle, auf die der Trigger reagieren soll
• AFTER INSERT gibt an, wann der Trigger feuern soll, es gibt hier neben INSERT auch die Möglichkeit, auf DELETE oder UPDATE zu reagieren oder auf Kombinationen daraus (durch Kommas getrennt) angeben. Ein Trigger, der bei INSERT, UPDATE und DELETE feuert, ist ein “touch trigger”, der jede Veränderung an der Tabelle registriert. AFTER INSERT bedeutet, dass der TRIGGER erst ausgeführt wird, wenn alle Teile des einfügenden Statements erfolgreich abgeschlossen wurden.
• Dann folgt mit BEGIN und END ein Block, der definiert, was nach dem INSERT erfolgen soll, hierbei könnt ihr mit “inserted” bzw. “deleted” auf die neu eingefügten bzw. gelöschten oder überschriebenen Werte zugreifen. So einfach habt ihr euren ersten Trigger definiert. Fügt nun Datensätze in die FirstName-Tabelle ein:

INSERT INTO Trig.FirstNames
    (FirstName)
VALUES 
    ('Akasuki'), ('Emiko'), ('Saeko')

Fragt ihr nun die FirstNames-Tabelle ab, so seht ihr wenig überraschend drei Einträge mit den IDs 1, 2 und 3. Durch den Trigger wurden diese Datensätze nun aber auch in die FullNames-Tabelle geschrieben. Hier allerdings in umgekehrter Reihenfolge, so dass “Saeko” mit der FirstNameId 3 die FullNameId 10 hat während “Akasuki” mit der FirstNameId 1 die FullNameId 30 hat. Das liegt daran, dass wir unseren Trigger als AFTER INSERT definiert hatten, was bedeutet, dass die TRIGGER-Statements erst ausgeführt werden, wenn alle drei INSERTs erfolgreich waren. In welcher Reihenfolge die Datensätze dann in die FullName-Tabelle eingefügt werden, ist dem SQL Server überlassen, der die durch den Trigger abgefeuerten Statements auf einen LIFO Call Stack legt, der nach dem erfolgreichen Abschluss des INSERT Statements abgearbeitet wird. Genauso wie wir nun einen AFTER INSERT–TRIGGER definiert haben, könnt ihr INSTEAD OF INSERT–Trigger schreiben, die das INSERT-Statement abfangen und umleiten. Dafür modifizieren wir unseren Insert-Trigger noch einmal:

ALTER TRIGGER Trig.TestInsertTrigger 
ON Trig.FirstNames
INSTEAD OF INSERT AS 
BEGIN
    SET NOCOUNT ON;
    INSERT INTO Trig.FullNames 
        (FirstName, FirstNameId) 
    SELECT 
        i.FirstName, i.FirstNameId 
    FROM inserted i 
END

Nun fügen wir erneut eine Zeile in die FirstName-Tabelle ein:

INSERT INTO Trig.FirstNames
    (FirstName)
VALUES 
    ('Sato')

Fragt ihr nun die beiden Tabellen ab, so seht ihr in der FirstNames-Tabelle drei Einträge (“Akasuki”, “Emiko” und “Saeko”), in der FullNames-Tabelle aber noch zusätzlich den Eintrag von “Sato”:

SELECT * FROM Trig.FirstNames
SELECT * FROM Trig.FullNames

Wichtig bei der Arbeit mit Triggern: Da Trigger zeilenweise operieren, können sie für die Performance einer Datenbank tödlich sein. Wenn ein einfaches INSERT viele andere INSERTs nach sich zieht, ist das für den Endanwender nicht nachvollziehbar. Es empfiehlt sich also, mit TRIGGERn sparsam umzugehen in euren Datenbanken.

Außerdem verschlechtert sich die Verständlichkeit eurer Anwendung erheblich, wenn beim INSERT in Tabelle A auf einmal Daten in Tabelle B auftauchen statt in Tabelle A. INSTEAD OF DELETE-Trigger können aber auch nützlich sein, wenn ihr mit IsDeleted-Flags arbeiten möchtet, anstatt Datensätze zu löschen, denn durch den INSTEAD OF DELETE-Trigger kann das versehentliche Löschen von Daten verhindert werden. Seid trotzdem vorsichtig bei der Arbeit mit TRIGGERn.

Referenzen

• Dokumentation von Microsoft