Wenn einem das Wasser bis zum Halse steht, soll man den Kopf nicht hängen lassen.
emde IT-LÖSUNGEN
> Tel. 08131 / 99 69 80-0
> Kontakt per E-Mail
INSTEAD OF-Trigger
22. April 2014
Was ist ein INSTEAD OF-Trigger?
Denjenigen, die sich ein wenig mit Datenbanken auskennen, ist klar, was ein Trigger ist: Eine Prozedur, die beim Einfügen, Ändern oder Löschen von Datensätzen in einer Tabelle aufgerufen wird und bestimmte Überprüfungen oder Anpassungen von Daten vornimmt. Was aber ein INSTEAD OF-Trigger sein soll, ist demhingegen erst einmal rätselhaft.
Bei Sichten (Views), die sich auf mehrere mit einem JOIN verbundenen Tabellen beziehen, weiß der SQL-Server nicht genau, was beim Einfügen, Ändern und Löschen von Inhalten genau zu tun ist. Darum sind solche Views in der Regel nicht aktualisierbar. Ein INSTEAD OF-Trigger schafft in diesem Fall Abhilfe.
Das Vorgehen soll am folgenden Beispiel erläutert werden: Bei einem Projekt haben wir in einer Tabelle Vergütungen gespeichert. Es gibt nun drei unterschiedliche Vergütungsarten: Basis-Vergütungen, Auftraggebern zugeordnete und einzelnen Aufträgen zugeordnete Vergütungen. Uns soll in diesem Zusammenhang im wesentlichen die m:n-Beziehung zwischen den Auftraggebern und den Vergütungen interessieren, die mit Hilfe der Tabelle Auftraggeber_Verguetungen realisiert wird. Dabei ist die Besonderheit zu beachten, dass den Auftraggebern sowohl die Basisvergütungen als Verknüpfung als auch eigene Vergütungen zugeordnet werden können. Basisvergütungen erkennt man daran, dass deren ID in der Tabelle Verguetungen_Basis gespeichert ist. Die entsprechende Datenstruktur zeigt der untenstehende Screenshot:
In unserem Beispiel geht es konkret um den View Verguetungen_Auftraggeber_V, in dem Werte aus den beiden Tabellen Verguetungen und Auftraggeber_Verguetungen miteinander kombiniert werden.
SELECT dbo.Auftraggeber_Verguetungen.Ver_ID,
dbo.Verguetungen.Verguetung,
dbo.Verguetungen.AufKre_ID,
dbo.Auftraggeber_Verguetungen.AufGebVer_ID,
dbo.Auftraggeber_Verguetungen.AufGeb_ID,
dbo.Auftraggeber_Verguetungen.Standard,
dbo.Verguetungen.TSVerguetungen,
dbo.Auftraggeber_Verguetungen.TSAuftraggeber_Verguetungen,
CASE
WHEN Exists(SELECT Ver_ID
FROM Verguetungen_Basis
WHERE Verguetungen_Basis.Ver_ID = Verguetungen.Ver_ID)
THEN 'Referenz'
ELSE 'Kopie'
END AS Art
FROM dbo.Verguetungen
INNER JOIN dbo.Auftraggeber_Verguetungen
ON dbo.Verguetungen.Ver_ID = dbo.Auftraggeber_Verguetungen.Ver_ID
Soweit sollte alles klar sein: Der View gibt Daten aus den beiden Tabellen aus, markiert Basisvergütungen mit dem Marker "Referenz" und auftraggebereigene Vergütungen mit dem Marker "Kopie".
Nun kommen die INSTEAD OF-Trigger ins Spiel. Schauen wir uns vielleicht zuerst den einfachsten Fall an, in dem Datensätze aus dem View gelöscht werden. Hier ist der Quelltext:
CREATE TRIGGER [dbo].[Verguetungen_Auftraggeber_V_DELETE]
on [dbo].[Verguetungen_Auftraggeber_V] INSTEAD OF DELETE
AS
BEGIN
IF (SELECT COUNT(*) FROM Deleted) > 0
BEGIN
/*
Datensatz in der Tabelle Auftraggeber_Verguetungen löschen
*/
DELETE Auftraggeber_Verguetungen
FROM Auftraggeber_Verguetungen
JOIN deleted
ON Auftraggeber_Verguetungen.Ver_ID = deleted.Ver_ID
/*
Datensatz in der Tabelle Verguetungen nur löschen, wenn es sich nicht
um eine Basis-Vergütung handelt.
*/
DELETE Verguetungen
FROM Verguetungen
JOIN deleted
ON Verguetungen.Ver_ID = deleted.Ver_ID
WHERE NOT EXISTS (SELECT Ver_ID
FROM Verguetungen_Basis AS VB
WHERE VB.Ver_ID = Verguetungen.Ver_ID)
END
END
Die Kommentare im obigen Quelltext beschreiben die Funktion des Codes eigentlich genau. Für das Verständnis des INSTEAD OF-Trigger ist es wichtig, sich zu vergegenwärtigen, dass die Datenbank keine automatische Löschung von Datensätzen durchführt, sondern lediglich die Dinge unternimmt, die im INSTEAD OF-Trigger beschrieben sind. In diesem Fall wird demgemäß in jedem Fall der Datensatz in der Tabelle Auftraggeber_Vergütungen gelöscht. Die zugehörige Vergütung wird aber nur dann entfernt, wenn es sich nicht um eine Basisvergütung handelt.
Schauen wir uns als nächstes den Insert-Fall an:
CREATE TRIGGER [dbo].[Verguetungen_Auftraggeber_V_INSERT]
on [dbo].[Verguetungen_Auftraggeber_V] INSTEAD OF INSERT AS
BEGIN
DECLARE @Ver_ID int
DECLARE @Verguetung nvarchar(50)
DECLARE @AufKre_ID int
DECLARE @AufGeb_ID int
DECLARE @Standard smallint
DECLARE INS CURSOR LOCAL FORWARD_ONLY FOR
SELECT Ver_ID, Verguetung, AufKre_ID, AufGeb_ID, Standard FROM inserted;
IF (SELECT COUNT(*) FROM inserted) > 0
BEGIN
OPEN INS;
FETCH NEXT FROM INS
INTO @Ver_ID, @Verguetung, @AufKre_ID, @AufGeb_ID, @Standard;
WHILE @@FETCH_STATUS = 0
BEGIN
/*
Neue Vergütung anlegen, wenn keine Ver_ID angegeben wird
*/
If @Ver_ID is null
BEGIN
INSERT INTO Verguetungen (Verguetung, AufKre_ID)
VALUES (@Verguetung, @AufKre_ID)
SET @Ver_ID = @@IDENTITY
END
/*
Neue Vergütung als Auftraggeber-spezifisch markieren
*/
INSERT INTO Auftraggeber_Verguetungen (AufGeb_ID, Ver_ID, Standard)
VALUES (@AufGeb_ID, @Ver_ID, @Standard)
FETCH NEXT FROM INS
INTO @Ver_ID, @Verguetung, @AufKre_ID, @AufGeb_ID, @Standard;
END
CLOSE INS;
DEALLOCATE INS;
END
END
In der While-Schleife werden alle eingefügten Datensätze durchlaufen. Ist in diesen Datensätzen der Verweis auf die Vergütung in Form in der Variablen Ver_ID bereits enthalten, muss nichts getan werden. Ansonsten wird für jeden eingefügten Datensatz eine neue Vergütung angelegt und dem neuen Datensatz zugeordnet. Ist dies geschehen, wird der Datensatz in die Tabelle Auftraggeber_Verguetungen gespeichert.
Als letztes folgt der Trigger für die Update-Aktion:
CREATE TRIGGER [dbo].[Verguetungen_Auftraggeber_V_UPDATE]
on [dbo].[Verguetungen_Auftraggeber_V] INSTEAD OF UPDATE AS
BEGIN
DECLARE @AufGebVer_ID int
DECLARE @Ver_ID int
DECLARE @Verguetung nvarchar(50)
DECLARE @AufKre_ID int
DECLARE @AufGeb_ID int
DECLARE @Standard smallint
DECLARE INS CURSOR LOCAL FORWARD_ONLY FOR
SELECT AufGebVer_ID, Ver_ID, Verguetung, AufKre_ID, AufGeb_ID, Standard FROM inserted;
IF (SELECT COUNT(*) FROM inserted) > 0
BEGIN
OPEN INS;
FETCH NEXT FROM INS
INTO @AufGebVer_ID, @Ver_ID, @Verguetung, @AufKre_ID, @AufGeb_ID, @Standard;
WHILE @@FETCH_STATUS = 0
BEGIN
/*
Neue Vergütung anlegen, wenn keine Ver_ID angegeben wird.
Die Access-Applikation markiert neu anzulegende Datensätze mit Ver_ID = 0
*/
If @Ver_ID = 0
BEGIN
INSERT INTO Verguetungen (Verguetung, AufKre_ID)
VALUES (@Verguetung, @AufKre_ID)
SET @Ver_ID = @@IDENTITY
END
ELSE
BEGIN
UPDATE Verguetungen
SET Verguetung = @Verguetung,
AufKre_ID = @AufKre_ID
WHERE Ver_ID = @Ver_ID And
Not Exists (SELECT Ver_ID
FROM Verguetungen_Basis
WHERE Verguetungen_Basis.Ver_ID = Verguetungen.Ver_ID)
END
/*
Datesatz in der Tabelle Auftraggeber_Verguetungen aktualisieren
*/
UPDATE Auftraggeber_Verguetungen
SET AufGeb_ID = @AufGeb_ID,
Ver_ID = @Ver_ID,
Standard = @Standard
WHERE AufGebVer_ID = @AufGebVer_ID
FETCH NEXT FROM INS
INTO @AufGebVer_ID, @Ver_ID, @Verguetung, @AufKre_ID, @AufGeb_ID, @Standard;
END
CLOSE INS;
DEALLOCATE INS;
END
END
Ähnlich wie beim Einfügen von Datensätzen wird entschieden, für welche Datensätze neue Vergütungen angelegt werden müssen. Ansonsten werden die Daten einfach nur aktualisiert
Kommentare (0)
Keine Kommentare gefunden!