Trigger zum automatischen Aktualisieren des last_modified-Feldes bei Änderung eines Datensatzes

Stell dir vor, du entwickelst eine App zur Verwaltung von Studierenden und Kursen, und du hast eine Tabelle students. In dieser Tabelle gibt es das Feld last_modified, das jedes Mal automatisch aktualisiert werden soll, wenn sich Daten im Datensatz ändern (zum Beispiel wenn der Name oder das Alter des Studierenden geändert wird).

Statt in jedem SQL-Query das Update von last_modified manuell zu schreiben, bauen wir einen Trigger, der das für uns übernimmt.

Tabellenstruktur von students

Erstmal erstellen wir die Tabelle students, die wir im Beispiel verwenden. Diese Tabelle enthält Basisinfos über die Studierenden:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY, -- Einzigartige ID des Studierenden
    name VARCHAR(100) NOT NULL,   -- Name des Studierenden
    age INT,                      -- Alter des Studierenden
    last_modified TIMESTAMP NOT NULL DEFAULT NOW() -- Zeitpunkt der letzten Änderung
);

• Das Feld last_modified wird beim Anlegen des Datensatzes mit der aktuellen Zeit (NOW()) befüllt.
• Dieses Feld wird automatisch aktualisiert, wenn sich die Daten des Studierenden ändern.

Füllen wir die Tabelle mal mit ein paar Testdaten:

INSERT INTO students (name, age)
VALUES 
    ('Otto Lin', 20),
    ('Maria Chi', 22),
    ('Alex Song', 19);

Jetzt sieht die Tabelle so aus:

Funktion zum Aktualisieren von last_modified erstellen

Die Funktion in PL/pgSQL wird vom Trigger genutzt, um das Feld last_modified zu aktualisieren. Sie wird automatisch vor der Änderung eines Datensatzes aufgerufen.

Erstellen wir die Funktion update_last_modified:

CREATE OR REPLACE FUNCTION update_last_modified()
RETURNS TRIGGER AS $$
BEGIN
    -- Aktualisiere das Feld last_modified auf die aktuelle Zeit
    NEW.last_modified := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

• NEW ist eine spezielle Variable, die die neuen Daten des Datensatzes (nach der Änderung) enthält.
• Wir setzen für NEW.last_modified den Wert NOW() (aktuelles Datum und Uhrzeit).
• Die Funktion gibt die aktualisierte Variable NEW zurück, was für den Trigger notwendig ist.

Trigger erstellen

Jetzt bauen wir den Trigger, der die Funktion update_last_modified bei jedem Update eines Datensatzes in der Tabelle students automatisch aufruft.

CREATE TRIGGER set_last_modified
BEFORE UPDATE ON students
FOR EACH ROW
EXECUTE FUNCTION update_last_modified();

Was passiert hier:

• BEFORE UPDATE sagt, dass der Trigger vor dem Update ausgelöst wird.
• FOR EACH ROW bedeutet, dass der Trigger für jede geänderte Zeile ausgeführt wird.
• EXECUTE FUNCTION update_last_modified() ruft die Funktion update_last_modified auf.

Trigger testen

Jetzt testen wir mal, wie unser Trigger funktioniert. Wir holen uns die aktuellen Daten aus der Tabelle students:

SELECT * FROM students;

Ergebnis:

Jetzt ändern wir das Alter des Studierenden mit student_id = 1:

UPDATE students
SET age = 21
WHERE student_id = 1;

Nochmal die Daten abfragen:

SELECT * FROM students;

Erwartetes Ergebnis:

Achte drauf: Das Feld last_modified für den Datensatz mit student_id = 1 wurde auf die aktuelle Zeit gesetzt, während die anderen Zeilen unverändert blieben.

Trigger-Logik erweitern

Angenommen, wir wollen jetzt, dass das Feld last_modified nur aktualisiert wird, wenn bestimmte Spalten geändert werden. Zum Beispiel soll der Trigger nur feuern, wenn sich der Name oder das Alter des Studierenden ändert, aber nicht bei anderen Änderungen.

Dafür kann man eine Bedingung mit dem WHEN-Operator im Trigger-Definition hinzufügen.

Erstellen wir einen neuen Trigger mit Bedingung:

DROP TRIGGER IF EXISTS set_last_modified ON students;

CREATE TRIGGER set_last_modified
BEFORE UPDATE ON students
FOR EACH ROW
WHEN (OLD.name IS DISTINCT FROM NEW.name OR OLD.age IS DISTINCT FROM NEW.age)
EXECUTE FUNCTION update_last_modified();

Hier:

• Die WHEN-Bedingung prüft, ob sich die alten Werte (OLD) von den neuen (NEW) für die Spalten name und age unterscheiden.
• Wenn keine dieser Spalten geändert wurde, wird der Trigger nicht ausgelöst.

Probieren wir nochmal ein Update und testen die neue Logik.

Tipps für den Umgang mit Triggern

1. Übertreib es nicht mit Triggern. Sie sind praktisch, können aber die Logik der Datenbank verkomplizieren und das Debugging erschweren.
2. Dokumentiere immer, was ein Trigger macht und wann er verwendet wird.
3. Nutze WHEN-Bedingungen, um unnötige Trigger-Aufrufe zu vermeiden.
4. Denk dran, dass Trigger die Performance der Datenbank beeinflussen können, besonders wenn die Tabelle viele Zeilen hat.

Typische Fehler beim Arbeiten mit Triggern

Falsches Ändern von Daten. Zum Beispiel hast du vergessen, den Wert für NEW zu setzen und gibst die Originaldaten ohne Änderung zurück.

Falsche Bedingungen. Zum Beispiel hast du vergessen, eine WHEN-Bedingung hinzuzufügen, und der Trigger feuert auch dann, wenn eigentlich nichts geändert werden muss.

Rekursion. Wenn der Trigger eine Funktion aufruft, die wiederum den Trigger auslöst, kannst du aus Versehen eine Endlosschleife bauen. PostgreSQL schützt zwar vor Rekursion, aber besser ist es, sowas zu vermeiden.

Dieses Beispiel zeigt, wie Trigger dir das automatische Aktualisieren von Daten erleichtern können. In echten Projekten wird diese Technik oft fürs Logging von Änderungen, zur Sicherung der Datenintegrität und zur Automatisierung von Routineaufgaben genutzt.