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 sollAFTER INSERT
gibt an, wann der Trigger feuern soll, es gibt hier nebenINSERT
auch die Möglichkeit, aufDELETE
oderUPDATE
zu reagieren oder auf Kombinationen daraus (durch Kommas getrennt) angeben. Ein Trigger, der beiINSERT
,UPDATE
undDELETE
feuert, ist ein “touch trigger”, der jede Veränderung an der Tabelle registriert.AFTER INSERT
bedeutet, dass derTRIGGER
erst ausgeführt wird, wenn alle Teile des einfügenden Statements erfolgreich abgeschlossen wurden.- Dann folgt mit
BEGIN
undEND
ein Block, der definiert, was nach demINSERT
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 INSERT
s 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 INSERT
s 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.