5. Trigger

Wir möchten verhindern, das sich unsere Kunden überschulden und wir als Bank uns auch. Daher fügen wir an die Stammdatentabelle ein Feld 'dispo' an, welches einen maximalen Dispo für jeden Kunden definiert. Nun brauchen wir einen Mechanismus, der bei Eingabe einer Buchung prüft, ob das Konto nicht überzogen wird. Dazu definieren wir einen Trigger.

001 --
002 -- als erstes die Spalte den Dispo an die Stammdaten anfügen
003 -- zusätzlich setzen wir einen Default Wert für den Dispo
004 --
005 ALTER TABLE kunden ADD COLUMN dispo decimal(10,2);
006 ALTER TABLE kunden ALTER COLUMN dispo SET DEFAULT 0.00;
007 
008 
009 --
010 -- nun erstellen wir die Triggerfunktion
011 --
012 CREATE OR REPLACE FUNCTION check_dispo() RETURNS TRIGGER AS '
013         DECLARE
014           _current numeric(10,2);
015           _dispo numeric(10,2);
016         BEGIN
017           -- Hinweis: NEW ist ein Array, das von PostgreSQL automatisch
018           --          bereitgestellt wird und die Daten der Eingabe enthält,
019           --          so, wie sie geschrieben würden
020           --          Das Gegenstück OLD enthält die alten Daten, die vorher
021           --          in der entsprechenden Zeile standen, z.B. bei einem Update
022 
023           IF NEW.typ = ''Z'' THEN 
024             -- ein Zugang zum Konto kann das Konto nicht negativ werden lassen
025             return NEW;
026           END IF;
027         
028           -- aktuellen Kontowert holen
029           SELECT INTO _current SUM(CASE WHEN typ=''Z''
030                                         THEN wert
031                                         ELSE wert * -1
032                                     END) 
033                  FROM buchungen
034                 WHERE kontonr = NEW.kontonr;
035           IF _current IS NULL THEN
036             -- wenn es noch keine Buchungen gibt, erhalten wir NULL als Ergebnis
037             -- damit würde später unsere Berechnung jedoch nicht funktionieren
038             _current := 0.0;
039           END IF;
040 
041           -- erlaubten Dispo dieses Kunden holen
042           SELECT INTO _dispo dispo * -1 FROM kunden WHERE kontonr = NEW.kontonr;
043 
044           -- Dispo überprüfen
045           IF _dispo > (_current - NEW.wert) THEN
046             -- mit dieser Buchung wäre der Dispo überzogen,
047             -- also eine Fehlermeldung auslösen und die Buchung abbrechen
048             RAISE EXCEPTION ''Dispo überschritten, Buchung wird abgebrochen'';
049           END IF;
050 
051         RETURN NEW;
052 END;
053 ' LANGUAGE plpgsql VOLATILE;
054 
055 
056 --
057 -- diese definierte Funktion wird nun unser Trigger, den wir an
058 -- die Tabelle mit den Buchungen hängen
059 --
060 CREATE TRIGGER dispocheck BEFORE INSERT OR UPDATE ON buchungen
061     FOR EACH ROW EXECUTE PROCEDURE check_dispo();
062 
063 
064 --einigen Kunden einen Dispo gewähren
065 UPDATE kunden SET dispo=500 WHERE kundenname='Kunde 1';
066 UPDATE kunden SET dispo=750 WHERE kundenname='Kunde 3';
067 UPDATE kunden SET dispo=250 WHERE kundenname='Kunde 5';
068 
069 -- prüfen, ob das Finanzamt wirklich keinen Zugriff auf den Dispowert hat
070 SELECT * FROM view_kunden;

Links

CREATE TRIGGER