SQL-Trigger

SQL-Trigger: Ausführbare Logik

Durch den Einsatz von SQL-Triggern haben wir die Möglichkeit, auch unabhängig von den Begrenzungen eines Container-Typs Funktionen umzusetzen.

Mit Hilfe von kompakten Statements können wir Daten erfragen, verändern und erzeugen.

Dabei lassen sich neue Verknüpfungen, Berechnungen, Überprüfungen von Werten und auch logische Fallunterscheidung in wenigen Code-Zeilen umsetzen.

Namen aus Feldern generieren

Um Ihnen den Einstieg in die Trigger-Programmierung zu erleichtern, werden wir im Folgenden die Formel zur Bezeichnung eines Containers in einem SQL-Trigger festlegen.

Hinweis: Diese Funktion ist inzwischen auch über die Container-Verwaltung zugänglich. In Spezialfällen (Berechnungen, Logik im Bezug auf andere Container) ist jedoch weiterhin der folgende Weg notwendig.

Zunächst der einfachste Fall: man habe einen Container-Typ Kontakt mit den Feldern Name und Vorname.

Kontakt hat in diesem Beispiel die ID 3. Aus der container_steuerelemente, oder aus dem Client (Einrichtung → Zeige IDs), holt man sich deren IDs:

SELECT id, Bezeichnung, kategorie FROM container_steuerelemente WHERE containertyp=3 AND Bezeichnung LIKE '%name%'

Das Ergebnis könnte z.B. sein:

16  Vorname	0
17  Nachname    0

Daraus folgt, dass es eine Tabelle zz_3_0 (ID des Container-Typs, Reiter innerhalb dessen nach TabOrder) gibt, die in den Spalten c16 und c17 die für uns interessanten Werte enthält:

SELECT datensatz_id, c16, c17 FROM zz_3_0

Ergibt:

5   Frank   Diegel
6   Heini   Meyer

Erreichen wollen wir jetzt, dass die Spalten bezeichnung und bezeichnung_lang in der Tabelle container automatisch aus diesen Spalten zusammengesetzt werden. Bei bezeichnung_lang ließe sich noch mehr eintragen, z.B. dahinter der Firmenname in Klammern.

Unter dbo.zz_3_0 → Trigger legen wir einen neuen an. Als Template verwenden wir dabei:

CREATE TRIGGER [dbo].[update_zz_3_0]
   ON  [dbo].[zz_3_0]
   for INSERT, UPDATE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
END

update_zz_3_0 ist nur ein Name, der jedoch in der gesamten Datenbank eindeutig sein muss; daher verwenden wir dieses Schema, das auch gleich den Tabellen-Namen beinhaltet. Der Trigger wird sowohl beim INSERT als auch beim UPDATE ausgeführt; in diesem Fall würde derzeit auch nur UPDATE ausreichen, aber häufig erweitert man den Trigger später um Abfragen, die nur beim UPDATE funktionieren — also lassen wir es gleich drin.

Bisher tut der Trigger nichts; SET NOCOUNT ON ist ein Standard-Befehl, der Trigger etwas beschleunigt.

Zwischen der Zeile und END fügen wir einen neuen Block hinzu:

    UPDATE container
    SET bezeichnung = cast(c17 + ', ' + c16 as varchar(50)),
    bezeichnung_lang = cast(c17 + ', ' + c16 as varchar(50))
    FROM zz_3_0
    WHERE datensatz_ID in (SELECT datensatz_id FROM inserted)
    AND container.ID = zz_3_0.datensatz_id

Angepasst werden hierbei lediglich:

• der Name der zz-Tabelle,
• das Statement zwischen cast( und as varchar(50)). Der Rest bleibt praktisch immer gleich.

Es werden also der Wert des Feldes mit der ID 17, ein , und eine Lücke und schließlich der Wert des Feldes mit der ID 16 zu einem varchar der Länge 50 zusammengefügt — z.B. “Diegel, Frank”.

Das klappt nur dann, wenn die Werte auch alle selbst vom Typ varchar sind. Bei einem DatumControl, einem TextControl mit numerischen Werten, etc., braucht es pro Feld einen expliziten Cast. Würden wir also z.B. eine Kundennummer davor haben wollen, die in einem Feld c13 läge, so sähe die Zeile wie folgt aus:

    [..]
    SET bezeichnung = cast(cast(c13 as varchar) + ': ' + c17 + ', ' + c16 as varchar(50)),
    [..]

Beispielsweise ergäbe dies “1337: Diegel, Frank”.

Hat man ein Container-Control — einen 1:1-Verweis auf einen anderen Container also — , so gibt es verschiedene Möglichkeiten, was davon übernommen werden soll: der im Control dargestellte Text, oder aber ein Feld im verknüpften Container. In der c-Spalte selbst ist nur die ID des Containers, auf den verwiesen wird, gespeichert. Dieser wird ist im Allgemeinen für den Anwender eher uninteressant. Ein einfaches cast(c58 as varchar) o.Ä. würde uns also wenig weiterhelfen. Für den Fall, dass wir den sichtbaren Namen wollen, brauchen wir die bezeichnung, z.B.:

    [..]
    SET bezeichnung = cast(cast(c13 as varchar) + ': ' + c17 + ', ' + c16 as varchar(50)),
    bezeichnung_lang = cast(cast(c13 as varchar) + ': ' + c17 + ', ' + c16 + ' (' + (SELECT bezeichnung FROM container WHERE id = c58) + ')' as varchar(100)),
    [..]

Ergebnis: “1337: Diegel, Frank (TRENZ AG)”.

Vielleicht aber ist die Bezeichnung hier nicht sinnvoll, sondern ein konkretes Feld. Gibt es einen Container-Typ Firma mit ID 1 und einem Feld 8 im Reiter mit TabOrder 0, das nur den Firmennamen enthält, so geht das wie folgt:

    [..]
    bezeichnung_lang = cast(cast(c13 as varchar) + ': ' + c17 + ', ' + c16 + ' (' + (SELECT c8 FROM zz_1_0 WHERE zz_3_0.datensatz_id = c58) + ')' as varchar(100)),
    [..]

Dies deckt die häufigeren Fälle ab.

Container einer Liste hinzufügen

Für Listen-Zuordnungen ist die Tabelle zContainer zuständig. Ein häufiger Fall ist, dass man einen Datensatz unter bestimmten Bedingungen einer Liste zuordnen möchte: ein Projekt zum Beispiel einer Liste Aktive Projekte, wenn dieses aktiv ist. Wir machen aber erstmal den einfacheren Fall, dass gespeicherte Projekte grundsätzlich in eine Liste kommen.

Wir brauchen folgende Informationen:

• die ID des Containers Projekte, in den alle Projekte sollen: ZuordnungsID, im Beispiel 500
• die ID des Reiters, in dem sich die Liste befindet: ZuordnungsKat, im Beispiel 75

Der Einfachheit halber schreiben wir den Trigger so, dass er nur einen Datensatz ändert. Es kommt nur bei Importen und anderen Sonderfällen vor, dass mehrere Datensätze gleichzeitig in der inserted-Tabelle sind, also hinzugefügt oder verändert wurden.

Wir legen wieder einen Trigger in der zz-Tabelle für Projekt an (im Beispiel zz_7_0), oder nehmen einen bestehenden. Wir brauchen einen Block folgenden Schemas:

    DECLARE @DatensatzID int
    SET @DatensatzID = (SELECT top 1 datensatz_id FROM inserted)
    IF not exists(SELECT * FROM zContainer WHERE ZuordnungsID = 500
        AND ZuordnungsTyp = 2 AND ZuordnungsKat = 75 AND DatensatzID = @DatensatzID)
    INSERT INTO zContainer(DatensatzID, ZuordnungsID, ZuordnungsTyp, ZuordnungsKat, aendat, aensab)
    VALUES(@DatensatzID, 500, 2, 75, getdate(), 9999)

Der ZuordnungsTyp ist derzeit immer 2, getdate() setzt das aktuelle Datum (denn die Zuordnung fand jetzt gerade statt) und 9999 ist eine Pseudo-UserID für automatische Vorgänge in Triggern, Stored Procedures, etc.

Die ersten beiden Zeilen legen eine lokale Variable @DatensatzID an, und setzen diese auf die ID des ersten (und zumeist einzigen) eingefügten Datensatzes. Die IF not exists-Kondition prüft, ob es die Zuordnung bereits gibt; ansonsten passiert nichts. Das INSERT-Statement schliesslich legt die Zuordnung an.

Stattdessen haben wir vielleicht eine Liste (ID 501) mit den zwei Reitern Aktiv (76) und Inaktiv (77). Für solch eine Unterscheidung hilft z.B. eine Checkbox Aktiv (c83), so dass wir diese auf = 1 prüfen können, oder ein DropDown Status mit dem möglichen Wert “Aktiv”, das wir auf auf = 'Aktiv' prüfen.

Jetzt ersetzen wir den Block wie folgt:

    IF (SELECT top 1 c83 FROM inserted) = 1
        IF not exists(SELECT * FROM zContainer WHERE ZuordnungsID = 501
            AND ZuordnungsTyp = 2 AND ZuordnungsKat = 76 AND DatensatzID = @DatensatzID)
        INSERT INTO zContainer(DatensatzID, ZuordnungsID, ZuordnungsTyp, ZuordnungsKat, aendat, aensab)
        VALUES(@DatensatzID, 501, 2, 76, getdate(), 9999)
        DELETE FROM zContainer WHERE ZuordnungsID = 501
            AND ZuordnungsTyp = 2 AND ZuordnungsKat = 77 AND DatensatzID = @DatensatzID
    ELSE
        IF not exists(SELECT * FROM zContainer WHERE ZuordnungsID = 501
            AND ZuordnungsTyp = 2 AND ZuordnungsKat = 77 AND DatensatzID = @DatensatzID)
        INSERT INTO zContainer(DatensatzID, ZuordnungsID, ZuordnungsTyp, ZuordnungsKat, aendat, aensab)
        VALUES(@DatensatzID, 501, 2, 77, getdate(), 9999)
        DELETE FROM zContainer WHERE ZuordnungsID = 501
            AND ZuordnungsTyp = 2 AND ZuordnungsKat = 76 AND DatensatzID = @DatensatzID
    END

Dies fügt die Zuordnung für die jeweils relevante Liste ein, und löscht sie — falls vorhanden — automatisch auch aus der jeweils anderen.

Alle Felder aller Datensätze eines Typs neu berechnen

Da viele Trigger in zz-Tabellen nur dafür ausgelegt sind, dass sich ein einzelner Datensatz in der inserted-Tabelle befindet (also nur einer eingefügt oder veränder wurde), kann man häufig keine Berechnungen für alle Datensätze mit einem einfachen UPDATE-Statement durchführen. Hier hilft ein Cursor, der das Statement für jeden Datensatz einzeln ausführt. Bei einer Tabelle zz_5_0 sähe das so aus:

DECLARE @DatensatzID int
DECLARE DatensatzCursor cursor for
    SELECT datensatz_id FROM zz_5_0
OPEN DatensatzCursor
FETCH next FROM DatensatzCursor into @DatensatzID
WHILE @@fetch_status <> -1
BEGIN
    UPDATE zz_5_0
    SET aendat = getdate()
    WHERE datensatz_id = @DatensatzID
    FETCH next FROM DatensatzCursor into @DatensatzID
END
CLOSE AntragCursor
DEALLOCATE AntragCursor