Benutzer Trigger
Zuletzt geändert: 11.06.2023 13:20

Benutzer-Trigger (EULANDA SQL-API) #

Trigger stellen ein elegante Methode dar, eigene Funktionen aber auch Änderungen an Abläufen, in die Warenwirtschaft EULANDA® zu integrieren.

Was sind Trigger #

Trigger sind eine Sonderform von gespeicherten Prozeduren. Sie können nahezu beliebige SQL-Befehle enthalten und werden vom SQL-Server automatisch bei bestimmten Datenbankoperationen (Einfügen, Ändern, Löschen von Datensätzen) aufgerufen.

Die Möglichkeit im Trigger Fehlermeldungen auszugeben, die der Endanwender angezeigt bekommt und die Möglichkeit die ursprünglichen Datenbankoperationen, die den Trigger ausgelöst haben, wieder vollständig rückgängig (Rollback) zu machen, eröffnet ein weites Spektrum an Anwendungsmöglichkeiten.

Einige der möglichen Aufgaben für einen Trigger sind:

  • Überprüfung von geänderten Daten auf deren Konsistenz. Und ggf. die Ausgabe von Fehlermeldungen und das Rückgängigmachen der Änderungen.
  • Pflege von abhängigen Daten
  • Löschen nicht mehr benötigter Daten
  • Bildung von Summen in übergeordneten Tabellen. Beispielsweise wird durch Änderung der Auftragspositionen der Gesamtpreis im Auftragskopf automatisch mitgepflegt.
  • Protokollieren von Änderungen z.B. zur Überwachung einzelner Benutzer etc.
  • aber auch ganz andere Aktionen, wie beispielsweise das automatische Versenden eines E-Mails beim Umwandeln eines Auftrags in einen Lieferschein.

Trigger werden einzelnen Tabellen zugeordnet. Und zu jedem Trigger wird festgelegt, bei welchen Operationen er ausgelöst (engl. triggered) werden soll. Diese Operationen sind:

INSERT, UPDATE und DELETE

Ein einzelner Trigger kann auch mehrere dieser Operationen gleichzeitig überwachen. Aber der selbe Trigger kann nicht bei mehreren Tabellen hinterlegt werden, auch wenn die verwendeten Tabellenspalten gleich lauten.

EULANDA® definiert bereits zu fast allen Tabellen eigene Trigger, die die Business-Logik abbilden. Microsoft SQL-Server® hat aber die angenehme Eigenschaft beliebig viele Trigger pro Tabelle zu hinterlegen. Ihnen als Entwickler sind also in dieser Hinsicht keine Grenzen in Punkto Erweiterbarkeit gesetzt.

Damit sich Benutzer-Trigger nahtlos in EULANDA® integrieren und nicht zu Fehlfunktionen führen müssen allerdings einige Dinge beachtet werden.

Regeln für das Anlegen von Benutzer-Triggern #

  1. Der Triggername muss den Namenskonventionen für SQL-Objekte entsprechen . Der Name muss mit TR_USER_ beginnen, gefolgt von dem Kürzel der jeweiligen Tabelle und dem Kürzel der auslösenden Tabellenoperation. Weitere Informationen finden Sie im Kapitel Namenskonventionen.
  2. Der erste Befehl im Trigger muss SET NOCOUNT ON sein. Ansonsten kann die Datenbankschnittstelle OLE/DB bzw. ADO die Änderungen nicht mehr korrekt zuordnen. Dies kann zu unvorhersehbaren Fehlern in EULANDA® führen.
  3. Es dürfen keinerlei SELECT-Befehle oder PRINT-Befehle ausgeführt werden, die Daten oder Informationen an die Anwendungs-Ebene zurückgeben. Dies führt ebenfalls zu Fehlfunktionen in EULANDA®. SELECT-Befehle, die lediglich lokalen Variablen Werte zuweisen sind dagegen erlaubt.
  4. Die eigentliche Datenbankoperation ist erst beendet, wenn alle Trigger abgearbeitet wurden. Der Trigger-Code hat also erheblichen Einfluss auf die Performance des Gesamtsystems. Es ist darauf zu achten, dass zeitintensive Befehle innerhalb eines Triggers nicht unnötig oft ausgeführt werden. Dies kann z.B. über Funktionen wie UPDATE() verhindert werden. Auf diese Problematik wird später noch genauer eingegangen.
  5. Wenn im Trigger ein ROLLBACK durchgeführt wird, muss unbedingt davor RAISERROR mit einer sinnvollen Fehlermeldung und einem Schweregrad von 16 ausgelöst werden. Ansonsten geht EULANDA® davon aus, dass kein Fehler aufgetreten ist.
  6. Trigger müssen die Tatsache berücksichtigen, dass die Datenbankoperation, die den Trigger ausgelöst hat auch mehr als eine oder eventuell gar keine Zeilen in der Tabelle betrifft. Dementsprechend haben die Pseudo-Tabellen inserted und deleted variierende Zeilenzahlen (siehe Beispiel C:)
Beispiel für einen Benutzer-Trigger 
CREATE TRIGGER TR_USER_AF_INSUPD_StatusPruefung ON Auftrag
FOR INSERT, UPDATE
AS

/* Dies muss der erste Befehl in einem Trigger sein */
SET NOCOUNT ON

/* Hier fangen die eigentlichen Trigger-Befehle an*/

Server-Belastung gering halten #

Prüfen der geänderten Felder mit UPDATE() #

Bis auf sehr wenige Ausnahmen möchte man in einem Trigger nur eine eingeschränkte Anzahl von Spalten überwachen. Transact-SQL liefert mit der Funktion UPDATE(spaltenname) die Möglichkeit die geänderten Spalten zu ermitteln. Kleiden Sie Ihre Trigger-Befehle stets in einen Bedingungs-Block ein, der alle gewünschten Felder enthält:

IF UPDATE(Status) OR UPDATE(Objekt) OR UPDATE (Datum)
BEGIN
  /* Ihre Trigger Befehle */
END

Hinweis: Die UPDATE-Funktion liefert in DELETE-Triggern immer False zurück.

Optimierte SELECTS und UPDATES #

Minimieren Sie bzw. Optimieren Sie SELECT oder INSERT Befehle. Legen Sie ggf. Indizes in den den entsprechenden Tabellen an, um die Ausführungszeit zu verkürzen.

Nur weil die Funktion UPDATE(spaltenname) True liefert, muss die Spalte nicht auch geändert worden sein. Z.B. ändert der Befehl

UPDATE Adresse SET RabattGr = 'A' WHERE RabattGr = 'A'

nicht die Rabattgruppe aber UPDATE spricht trotzdem an. Wenn ein aufwendiger SQL-Befehl von einer „echten“ Änderung der Spalte abhängt, sollten Sie weitere Prüfungen vorsehen. Folgender Befehl erzeugt beispielsweise eine temporäre Tabelle mit den Adress-IDs, die wirklich geändert wurden:

DECLARE @t TABLE (id int)
INSERT @t SELECT i.id
FROM inserted i, deleted d
WHERE i.id = d.id AND i.RabattGr <> d.RabattGr

Beispiele #

A: Überprüfen von Änderungen und Ausgabe einer Fehlermeldung mit Rollback #

In diesem Beispiel wird beim Ändern der Auftragspositionen überprüft, ob noch ein positiver Ertrag vorliegt. Wenn das nicht der Falls ist, wird eine Fehlermeldung ausgegeben und die Änderung verworfen (Hinweis: diese Überprüfung könnte auch effizienter mit einem CHECK-CONSTRAINT durchgeführt werden).

CREATE TRIGGER TR_USER_AFP_INSUPD_Ertrag ON AuftragPos
FOR INSERT, UPDATE
AS

SET NOCOUNT ON

IF ( UPDATE(VkRab) OR UPDATE(Menge) OR UPDATE(PreisEH) )
BEGIN
  IF EXISTS (SELECT * FROM inserted 
     WHERE (Menge > 0) AND (Ertrag < 0) ) BEGIN
    RAISERROR('[VENDOR:USER][ADRESS:USER]Die 
              Marge zu gering ist!', 16,1)
    ROLLBACK
  END
END
B: Erzeugen eines Protokoll-Eintrags im SQL-Server #

Dieser Trigger protokolliert Änderungen am Datum bzw. Bestellstatus des Auftrags. Hierbei werden nur Aufträge berücksichtigt, die älter als eine Stunde sind. Die Informationen werden im SQL-Server-Protokoll festgehalten. Dieses kann im SQL-Enterprise-Manager oder im Windows-Explorer als Textdatei eingesehen werden.

CREATE TRIGGER TR_USER_AF_UPD_Datum ON Auftrag
FOR UPDATE
AS

SET NOCOUNT ON

IF UPDATE(Datum) OR UPDATE(BestellStatus) 
BEGIN
  /* Überprüfen, ob Aufträge existieren, die älter als eine
  ** Stunde sind */
  IF EXISTS(SELECT * FROM inserted
    WHERE DATEDIFF(hour,CreateDate,GETDATE())>1 )
  BEGIN

    /* Deklaration der verwendeten Variablen */
    DECLARE @userid int, @nr int, @count int

    /* Ermitteln der Auftragsnummer und der Anzahl der
    ** geänderten Aufträge. Falls mehrere Aufträge betroffen sind
    ** wird in diesem Beispiel nur die kleinste Nummer ermittelt. */
    SELECT @nr = MIN(KopfNummer), @count = COUNT(*) FROM inserted

    /* Erzeugen eines Eintrags in Tabelle cnProesses
    ** mit allen Angaben zum Benutzer und Arbeitsplatz
    ** an dem der Befehl ausgeführt wird */
    EXEC cn_UserId @userid OUT

    /* Eintrag einer Fehlermeldung in das SQL-Server-Protokoll
    ** Der Schweregrad ist 1. Dadurch wird der Fehler 
    ** nicht an EULANDA weitergegeben */
    RAISERROR('Datum/BestellStatus in %d Auftragsposition(-en) 
      geändert (Auftrag #%d) 
      von Prozess %d (siehe Tabelle cnProcesses für Details)',
      1,1, @count, @nr, @userid) WITH LOG

  END
END
C: Ein Update Befehl der keine Zeilen ändert #

Folgendes SQL-Skript zeigt, dass ein UPDATE-Befehl nicht auch unbedingt Zeilen ändern muss. Für Trigger-Programmierer heißt das, dass man auch den Fall von Null Zeilen in den Pseudo-Tabellen berücksichtigen muss:

CREATE TRIGGER TR_USER_AR_UPD_CountTest ON Artikel
FOR UPDATE
AS

/*
** Dieser Trigger dient NUR zur Verdeutlichung der im
** Text beschriebenen Problematik.
** Dies ist ansonsten KEIN gültiger Trigger für die Verwendung
** mit der EULANDA®-Warenwirtschaft!
*/

IF UPDATE(Barcode)
  SELECT COUNT(*) [Betroffene Zeilen mit Barcode-Änderung] 
    FROM inserted
ELSE SELECT COUNT(*) [Keine Barcode Änderungen] FROM inserted

GO

UPDATE Artikel SET Barcode = '4711' WHERE 1 = 2

GO

DROP TRIGGER TR_USER_AR_UPD_CountTest

Siehe auch #

Namenskonventionen cn_UserId RAISERROR Erstellen von Benutzer-Indizes Erstellen von Benutzer-Check-Constraints