Trigger ruft Stored Procedure auf

Datenbanktrigger lösen bei Änderungen in der Datenbank eine Aktion aus. Bei den SQL-Befehlen »INSERT« , »UPDATE« und »DELETE« können Trigger jeweils entweder pro Datensatz (per Row) oder pro Befehl (per Statement) anschlagen. Soll ein Kunde im Shopsystem zum Beispiel ab der dritten Bestellung als Stammkunde gelten, lässt sich das über den in Abbildung 1 definierten Trigger realisieren. Der Dialog zeigt die Ausgaben der Datenbank nicht an.

Aus dem ersten Select-Statement ist ersichtlich, dass das Feld »stammkunde« vom Typ »BOOLEAN« ist. »Kunde1« ist zu diesem Zeitpunkt noch kein Stammkunde und hat noch keine Bestellung getätigt – die entsprechende Tabelle ist leer. Das dritte SQL-Statement erstellt die PL/PgSQL-Procedure »stammkunde()« . Wird der danach mit »CREATE TRIGGER« angelegte Trigger aktiv, ruft er genau die Stored Procedure auf.

Beim Anlegen des Triggers ist das Besondere, dass dieser als »AFTER INSERT« definiert ist. Daher "sieht" die ausführende Prozedur den neu eingefügten Datensatz bereits. Im letzten Drittel von Abbildung 1 ist zu sehen, wie nach dem dritten eingefügten Datensatz (Bestellvorgang) das Feld »stammkunde« auf »t« (true) wechselt, da die Bedingung »> 2« erfüllt ist.

Abbildung 1: Im Beispiel wird der definierte Trigger immer dann aktiv, wenn ein Kunde bestellt. Die jedes Mal aufgerufene Stored Procedure zählt die Transaktionen und ändert beim dritten Mal den Kundenstatus.

Einen Lagerbestand überwachen

Trigger lassen sich auch kaskadieren: In Listing 1 verringert jede Bestellung via Trigger in der Tabelle »lager« den Bestand. Ein Eintrag in der Tabelle »bestellung« löst dazu bei jedem Bestellvorgang einen Trigger aus (Zeilen 12 bis 14). Die zum Trigger gehörende Prozedur »lagerentnahme()« verringert die Lagermenge des bestellten Artikel um die bestellte Menge (Zeile 7). Vorher soll das Beispiel noch prüfen, ob im Lager auch genügend Bestand vorhanden ist, um die Bestellung zu bedienen (Zeile 3). Außerdem wird die entsprechende Tabellenzeile gleich blockiert, damit nicht eine gleichartige parallele Transaktion den Bestand ebenfalls verringern kann.

Da kein Lager unerschöpflich ist, sorgt Listing 2 für dessen Bestückung. Sinkt der Warenbestand unter ein in der Tabelle »artikel« definiertes Minimum (»min_lagermenge« ), platziert Zeile 7 in die Tabelle »zu_bestellen« einen Eintrag und löst damit die Bestellung einer ebenfalls in »artikel« definierten Mindestbestellmenge beim Lieferanten aus.

01 CREATE or REPLACE function lagerentnahme() RETURNS TRIGGER AS $$
02 BEGIN
03   IF (SELECT bestand FROM lager WHERE artikel_id=NEW.artikel_id FOR UPDATE) < NEW.menge THEN
04     RAISE EXCEPTION 'Nicht genuegend Bestand im Lager';
05   END IF;
06
07   UPDATE lager SET bestand = bestand - NEW.menge
08   WHERE artikel_id = NEW.artikel_id;
09   RETURN NEW;
10 END; $$LANGUAGE plpgsql;
11
12 CREATE TRIGGER trg_lagerentnahme
13   AFTER INSERT ON bestellung
14   FOR EACH ROW EXECUTE PROCEDURE lagerentnahme();

01 CREATE OR REPLACE function bestellen() RETURNS TRIGGER AS $$
02 BEGIN
03   IF (NEW.bestand < (SELECT min_lagermenge FROM artikel WHERE id=NEW.artikel_id)
04     -- pruefen, dass nicht schon eine Bestellung ausgeloest wurde
05     AND NOT (SELECT EXISTS(select 1 FROM zu_bestellen WHERE artikel_id = NEW.artikel_id FOR UPDATE)))
06   THEN
07     INSERT INTO zu_bestellen (
08       artikel_id,
09       menge
10     )
11     SELECT
12       NEW.artikel_id,
13       (SELECT min_bestellmenge FROM artikel WHERE id = NEW.artikel_id);
14   END IF;
15 RETURN NEW;
16 END; $$LANGUAGE plpgsql;
17
18 CREATE TRIGGER trg_bestellen AFTER INSERT ON lager FOR EACH ROW EXECUTE PROCEDURE bestellen();