Menu

PostgreSQL Trigger: CREATE TRIGGER mit Beispielen

Ein Trigger führt automatisch eine Funktion aus, wenn Zeilen eingefügt, aktualisiert oder gelöscht werden. In PostgreSQL schreibst du eine Triggerfunktion, die trigger zurückgibt, und hängst sie mit CREATE TRIGGER an eine Tabelle.

Diese Seite enthält ausführbare Editoren. Bearbeiten, ausführen und Ausgabe sofort sehen.

Ein Trigger lässt PostgreSQL automatisch eine Funktion aufrufen, wenn sich eine Tabelle ändert. Schreib zuerst die Triggerfunktion, deklariert mit RETURNS trigger, und häng sie dann an: CREATE TRIGGER name BEFORE UPDATE ON table FOR EACH ROW EXECUTE FUNCTION fn();. Der Klassiker ist eine updated_at Spalte, die aktuell bleibt:

Nur Post 1 wurde aktualisiert, also hat nur er einen Zeitstempel. NEW ist die Zeile, die gleich geschrieben wird; ein BEFORE Trigger kann sie ändern und muss sie zurückgeben. Dieselbe Funktion kannst du an jede Tabelle hängen, die eine updated_at Spalte hat.

BEFORE vs AFTER vs INSTEAD OF

ZeitpunktLäuftTypischer Einsatz
BEFOREbevor die Zeile geschrieben wirdNEW ändern oder prüfen oder die Zeile überspringen
AFTERnachdem die Zeile geschrieben wurdein andere Tabellen schreiben: Audit-Logs, Zähler
INSTEAD OFanstelle der Änderung, nur auf Viewseine View beschreibbar machen

FOR EACH ROW führt die Funktion einmal pro betroffener Zeile aus. FOR EACH STATEMENT (der Standard) führt sie einmal pro Anweisung aus, selbst wenn sich keine Zeile geändert hat. In einem Zeilentrigger enthält NEW die neue Zeile (INSERT, UPDATE) und OLD die vorherige (UPDATE, DELETE). TG_OP nennt die Operation, die ihn ausgelöst hat, und TG_TABLE_NAME die Tabelle.

Der Rückgabewert eines BEFORE Zeilentriggers entscheidet, was passiert: Gib NEW (geändert oder nicht) zurück, um weiterzumachen, oder NULL, um die Zeile ohne Fehler zu überspringen. Der Rückgabewert eines AFTER Triggers wird ignoriert, er gibt deshalb per Konvention NULL zurück.

Ein Audit-Log mit einem AFTER Trigger schreiben

Die Audit-Tabelle hält zwei INSERTs fest, ein UPDATE von 10 auf 12 und ein DELETE des Produkts zu 20. OLD ist bei einem Insert NULL und NEW bei einem Delete, daher das coalesce. Um ganze Zeilen zu speichern, legst du to_jsonb(OLD) und to_jsonb(NEW) in eine jsonb Spalte.

Eine Änderung blockieren oder überspringen

Ein BEFORE Trigger kann eine Änderung mit RAISE EXCEPTION ablehnen, was die Anweisung abbricht, oder eine Zeile überspringen, indem er NULL zurückgibt:

Das DELETE hat den Entwurf entfernt und die bezahlte Rechnung stillschweigend behalten. Ein BEFORE DELETE Trigger gibt OLD zurück, um das Löschen durchzulassen. Soll stattdessen die ganze Anweisung scheitern, ersetzt du RETURN NULL durch RAISE EXCEPTION 'invoice % is paid', OLD.id;. Ein Fehler lässt sich leichter debuggen als eine Zeile, die stillschweigend bleibt.

Nur feuern, wenn sich eine Spalte ändert

UPDATE OF column beschränkt einen Trigger auf Updates, die diese Spalte setzen. Eine WHEN Bedingung überspringt den Funktionsaufruf, wenn sie falsch ist. IS DISTINCT FROM behandelt NULL als Wert und erkennt deshalb auch Änderungen zu NULL oder von NULL weg:

Das erste Update setzt denselben Preis, das WHEN ist also falsch, und der Zähler bleibt bei 0. Das zweite ändert ihn, und price_changed endet bei 1.

Trigger auflisten, deaktivieren und löschen

-- triggers you created (excludes the ones behind foreign keys)
SELECT tgrelid::regclass AS table_name, tgname
FROM pg_trigger WHERE NOT tgisinternal;

ALTER TABLE products DISABLE TRIGGER products_price;
ALTER TABLE products ENABLE TRIGGER products_price;
DROP TRIGGER products_price ON products;

In psql listet \d products die Trigger einer Tabelle auf. CREATE OR REPLACE TRIGGER gibt es seit PostgreSQL 14 und EXECUTE FUNCTION seit PostgreSQL 11; älterer Code schreibt EXECUTE PROCEDURE, was weiterhin funktioniert. Ein Trigger, der in seine eigene Tabelle schreibt, kann sich selbst erneut auslösen, schütze dich also vor Schleifen (pg_trigger_depth() liefert die Verschachtelungstiefe). Das Kapitel zu Triggerfunktionen listet jede TG_ Variable auf, und Funktionen und Prozeduren behandelt PL/pgSQL selbst.

Häufig gestellte Fragen

Kann ein PostgreSQL Trigger ohne Funktion laufen?

Nein. CREATE TRIGGER ruft immer eine Funktion auf, die mit RETURNS trigger deklariert ist. Schreib zuerst die Funktion, meist in PL/pgSQL, und häng sie dann an.

In welcher Reihenfolge feuern Trigger auf derselben Tabelle?

Trigger derselben Art auf dasselbe Ereignis feuern in alphabetischer Reihenfolge ihrer Namen. Stell den Namen Zahlen oder Buchstaben voran, wenn die Reihenfolge wichtig ist.

Rollt ein Fehler im Trigger die Anweisung zurück?

Ja. Ein Fehler in einem Trigger, etwa durch RAISE EXCEPTION, bricht die Anweisung ab, die ihn ausgelöst hat, und die ganze Transaktion, außer du fängst ihn mit einem Savepoint ab.

Feuern Trigger bei TRUNCATE?

DELETE Trigger auf Zeilenebene nicht. Leg einen Trigger auf Anweisungsebene mit BEFORE TRUNCATE oder AFTER TRUNCATE an, um darauf zu reagieren.

Illustration der Programmiersprachen bei Coddy

Lerne mit Coddy zu programmieren

Los geht's