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:

Struktur der Tabellen Verguetungen, Verguetungen_Basis und Auftraggeber_Verguetungen

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