Automatisierung von Aufgaben mit Triggern

Stell dir vor, du bist Admin einer großen Datenbank. Plötzlich löscht jemand wichtige Infos aus einer Tabelle und alle rufen: "Wer war das?!". Um solche Situationen zu vermeiden, bieten Datenbanken die Möglichkeit, Änderungen zu protokollieren und nachzuvollziehen, was mit den Daten passiert ist. Das läuft alles über Logging und Audit.

• Änderungs-Logging speichert die Historie: welcher Datensatz wurde wie und wann geändert.
• Audit von Daten geht noch tiefer und speichert nicht nur die Änderungen, sondern auch Infos über den User, der sie ausgelöst hat.

Jetzt, wo du weißt, "warum", lass uns lernen, wie das "wie" funktioniert.

Erstellen einer Log-Tabelle

Bevor wir Trigger einrichten, brauchen wir eine Tabelle, um die Änderungen zu speichern. Hier ein Beispiel:

-- Erstellen einer Tabelle für das Logging von Änderungen
CREATE TABLE change_logs (
    log_id SERIAL PRIMARY KEY,       -- Einzigartige ID für den Log-Eintrag
    table_name TEXT NOT NULL,        -- Name der Tabelle, in der die Änderung passiert ist
    operation TEXT NOT NULL,         -- Typ der Operation: INSERT, UPDATE, DELETE
    change_time TIMESTAMP DEFAULT NOW(), -- Zeitpunkt der Änderung
    old_data JSONB,                  -- Daten vor der Änderung (für UPDATE/DELETE)
    new_data JSONB                   -- Daten nach der Änderung (für INSERT/UPDATE)
);

Was passiert hier?

1. log_id — eindeutige ID für jeden Log-Eintrag.
2. table_name — wir speichern, welche Tabelle geändert wurde.
3. operation — Typ der Operation: INSERT, UPDATE oder DELETE.
4. change_time — speichert den genauen Zeitpunkt der Änderung.
5. old_data und new_data — Daten vor und nach der Änderung im JSON-Format.

Logging von Änderungen mit einem Trigger

Jetzt, wo wir eine Log-Tabelle haben, lass uns einen Trigger für eine Tabelle erstellen, zum Beispiel students. Der soll alle Änderungen loggen: neue Studenten hinzufügen, Updates oder Löschungen. So gehen wir vor:

1. Wir schreiben eine Funktion in PL/pgSQL, die Einträge in die Log-Tabelle schreibt.
2. Wir erstellen einen Trigger auf der Tabelle students.

Die Funktion bekommt Infos über die Operation (INSERT, UPDATE, DELETE) und die geänderten Daten (OLD und NEW).

-- Funktion zum Schreiben von Änderungen in die Log-Tabelle
CREATE OR REPLACE FUNCTION log_student_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- Logging der INSERT-Operation
    IF TG_OP = 'INSERT' THEN
        INSERT INTO change_logs (table_name, operation, new_data)
        VALUES ('students', 'INSERT', row_to_json(NEW));

    -- Logging der DELETE-Operation
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO change_logs (table_name, operation, old_data)
        VALUES ('students', 'DELETE', row_to_json(OLD));

    -- Logging der UPDATE-Operation
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO change_logs (table_name, operation, old_data, new_data)
        VALUES ('students', 'UPDATE', row_to_json(OLD), row_to_json(NEW));
    END IF;

    RETURN NULL; -- Wir geben NULL zurück, weil das ein AFTER-Trigger ist
END;
$$ LANGUAGE plpgsql;

Hier gilt:

• TG_OP — spezielle Variable, die die aktuelle Operation enthält: INSERT, UPDATE, DELETE.
• row_to_json(OLD) und row_to_json(NEW) — wandeln die Zeilendaten ins JSON-Format um, damit sie bequem gespeichert werden können.
• RETURN NULL — da es ein AFTER-Trigger ist, sollen keine geänderten Daten zurückgegeben werden.

Jetzt verbinden wir unsere Funktion mit der Tabelle students.

-- Trigger zum Logging von Änderungen erstellen
CREATE TRIGGER students_log_trigger
AFTER INSERT OR UPDATE OR DELETE ON students
FOR EACH ROW
EXECUTE FUNCTION log_student_changes();

Was passiert hier?

• AFTER INSERT OR UPDATE OR DELETE — der Trigger feuert nach den genannten Operationen auf der Tabelle students.
• FOR EACH ROW — der Trigger läuft für jede geänderte Zeile.
• EXECUTE FUNCTION log_student_changes() — ruft unsere Logging-Funktion auf.

Trigger testen

Jetzt wird's Zeit, unseren Trigger zu testen.

1. Einfügen eines neuen Eintrags

INSERT INTO students (name, age, grade)
VALUES ('Otto Lin', 20, 'A');

Schauen wir, was in der Log-Tabelle steht:

SELECT * FROM change_logs;

Beispiel-Ergebnis:

2. Update eines Eintrags

UPDATE students
SET grade = 'B'
WHERE name = 'Otto Lin';

Nochmal die Log-Tabelle checken:

SELECT * FROM change_logs ORDER BY change_time DESC;

Ergebnis:

3. Löschen eines Eintrags

DELETE FROM students
WHERE name = 'Otto Lin';

Und wieder Log prüfen:

Praxisbeispiele

1. Logging von Operationen auf kritischen Tabellen: Zum Beispiel braucht eine Tabelle mit Bankkonten ein vollständiges Änderungsprotokoll, um Betrug zu verhindern.
2. System-Audit: Du kannst Einträge für Compliance oder Analyse der User-Aktivität speichern.
3. Datenwiederherstellung ermöglichen: Wenn jemand versehentlich Daten löscht, kannst du sie aus der Log-Tabelle wiederherstellen.

Besonderheiten und Stolpersteine

Beim Logging mit Triggern solltest du die Performance im Auge behalten. Wenn der Trigger sehr oft feuert, kann das die Datenbank belasten. Deshalb:

• Nutze Logging nur auf wirklich wichtigen Tabellen.
• Wenn das Log zu groß wird, entwickle Strategien zum Archivieren.

Mit Triggern ist es wie mit einer Gitarrensaite: Sie brauchen Feintuning, aber dann liefern sie einen tollen Sound, automatisieren Routineaufgaben und geben dir Kontrolle über deine Daten.