Zusammenspiel von Triggern mit PL/pgSQL-Funktionen: OLD, NEW, TG_OP

Trigger in PostgreSQL können nicht nur Funktionen als Reaktion auf bestimmte Aktionen ausführen, sondern geben diesen Funktionen auch coole Variablen mit. Genau dank dieser Variablen kannst du herausfinden, was mit den Daten in der Tabelle vor der Operation war, was danach daraus wurde und welche Operation überhaupt passiert ist.

• OLD — enthält die alten Daten der Tabellenzeile vor der Operation. Wird in Triggern für UPDATE und DELETE verwendet, weil es bei INSERT einfach nichts "Altes" gibt.
• NEW — enthält die neuen Daten der Tabellenzeile nach der Operation. Wird in Triggern für INSERT und UPDATE verwendet.
• TG_OP — enthält den Text der aktuellen Operation: INSERT, UPDATE oder DELETE.

Alle diese Variablen sind automatisch innerhalb der Funktion verfügbar, die mit dem Trigger verbunden ist.

Theorie ohne Praxis ist wie SQL ohne Indexe: langsam und traurig. Also schauen wir uns das Ganze an praktischen Beispielen an.

Verwendung von OLD für den Zugriff auf alte Daten

Stell dir vor, wir haben eine Tabelle students. Und jemand ändert dort das Alter eines Studenten (vielleicht hat sich ein Fehler eingeschlichen und man denkt, der Student ist noch 20 und nicht schon 25).

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT NOT NULL
);

Um nachzuvollziehen, was geändert wurde, erstellen wir eine Log-Tabelle:

CREATE TABLE student_changes (
    change_id SERIAL PRIMARY KEY,
    student_id INT NOT NULL,
    old_value INT,
    new_value INT,
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Jetzt geht's weiter: Wir erstellen eine Funktion, die die Änderungen loggt. Genau hier kommt OLD ins Spiel:

CREATE OR REPLACE FUNCTION log_student_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- Wir loggen die Änderung des Alters
    INSERT INTO student_changes (student_id, old_value, new_value)
    VALUES (OLD.id, OLD.age, NEW.age);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Und jetzt erstellen wir den Trigger:

CREATE TRIGGER student_age_update
AFTER UPDATE OF age ON students
FOR EACH ROW
WHEN (OLD.age IS DISTINCT FROM NEW.age) -- Nur ausführen, wenn sich das Alter geändert hat
EXECUTE FUNCTION log_student_changes();

Lass uns einen Studenten hinzufügen und dann eine Änderung machen:

INSERT INTO students (name, age) VALUES ('Alisa', 20);

UPDATE students
SET age = 25
WHERE name = 'Alisa';

-- Log der Änderungen prüfen:
SELECT * FROM student_changes;

Du wirst sehen, dass die Änderung im Log steht: Alter von 20 auf 25. Magie? Nope, OLD.

Verwendung von NEW für neue Daten

Jetzt stell dir vor, wir wollen beim Hinzufügen eines neuen Studenten automatisch seine ID und seinen Namen ins Log schreiben (ja, ein bisschen paranoid, aber manchmal ganz praktisch):

CREATE OR REPLACE FUNCTION log_new_student()
RETURNS TRIGGER AS $$
BEGIN
    -- Wir loggen die Daten des neuen Studenten
    INSERT INTO student_changes (student_id, old_value, new_value)
    VALUES (NEW.id, NULL, NEW.age); -- Es gibt keinen alten Wert, weil das ein INSERT ist
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER student_insert_log
AFTER INSERT ON students
FOR EACH ROW
EXECUTE FUNCTION log_new_student();

Wieder fügen wir einen neuen Studenten hinzu und prüfen das Log:

INSERT INTO students (name, age) VALUES ('Bob', 22);

-- Log prüfen:
SELECT * FROM student_changes;

Du wirst sehen, dass im Log ein neuer Student auftaucht. Das ist schon Datenpflege auf dem nächsten Level!

Verwendung von TG_OP zur Bestimmung des Operationstyps

Aber was, wenn wir einen universellen Trigger zum Loggen haben wollen, der INSERT, UPDATE und sogar DELETE abdeckt? Hier hilft die Variable TG_OP.

Wir erstellen eine universelle Funktion:

CREATE OR REPLACE FUNCTION log_all_operations()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (NEW.id, NULL, NEW.age);

    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (OLD.id, OLD.age, NEW.age);

    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (OLD.id, OLD.age, NULL);

    END IF;

    RETURN NULL; -- Für AFTER-Trigger bei DELETE gib NULL zurück
END;
$$ LANGUAGE plpgsql;

Wir erstellen einen Trigger, der bei allen drei Operationen feuert:

CREATE TRIGGER universal_student_log
AFTER INSERT OR UPDATE OR DELETE ON students
FOR EACH ROW
EXECUTE FUNCTION log_all_operations();

Wir fügen einen Studenten hinzu, ändern ihn und löschen ihn:

INSERT INTO students (name, age) VALUES ('Charlie', 30);
UPDATE students SET age = 31 WHERE name = 'Charlie';
DELETE FROM students WHERE name = 'Charlie';

-- Log prüfen:
SELECT * FROM student_changes;

Du kannst das Log aller Operationen sehen – ein Trigger, um sie alle zu kontrollieren!

Typische Fehler bei der Verwendung von OLD, NEW, TG_OP

Beim Arbeiten mit Triggern kann man auf ein paar typische Probleme stoßen:

"Warum funktioniert OLD nicht beim Einfügen?" Das ist das Standardverhalten: Für INSERT gibt es keine alten Daten. Verwende NEW.

"Was tun, wenn NEW beim Löschen nicht funktioniert?" Auch das ist erwartetes Verhalten: Für DELETE gibt es keine neuen Daten. Verwende OLD.

Die Trigger-Logik verursacht eine Endlosrekursion. Achte darauf, dass der Trigger sich nicht versehentlich selbst aufruft. Dafür kannst du klare Bedingungen im WHEN-Block setzen oder TG_OP prüfen.

2

Aufgabe

SQL SELF, Level 57, Lektion 4

Gesperrt

Protokollierung von Altersänderungen bei Studenten

Protokollierung von Altersänderungen bei Studenten

Öffnen

1

Umfrage/Quiz

Einführung in Trigger, Level 57, Lektion 4

Nicht verfügbar

Einführung in Trigger

Einführung in Trigger

Öffnen