PostgreSQL Trigger Automatisierung

Einführung

In diesem Lab lernen Sie, wie Sie Aufgaben in PostgreSQL mithilfe von Triggern automatisieren können. Der Schwerpunkt des Labs liegt auf der Erstellung einer Trigger-Funktion mit PL/pgSQL, um Gehaltsänderungen in einer employees-Tabelle automatisch in einer separaten salary_changes-Tabelle zu protokollieren.

Sie werden durch das Schreiben der Trigger-Funktion geführt, diese an ein Tabellenereignis (speziell eine Aktualisierung der employees-Tabelle) gebunden, das Verhalten des Triggers getestet und schließlich den Trigger deaktiviert und gelöscht.

Skills Graph

Tabellen und die Trigger-Funktion erstellen

In diesem Schritt erstellen Sie die Tabellen employees und salary_changes und definieren anschließend die Funktion log_salary_change mit PL/pgSQL.

Stellen Sie zunächst als Benutzer postgres eine Verbindung zur PostgreSQL-Datenbank her:

sudo -u postgres psql

Erstellen Sie nun die Tabelle employees:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

Dieser Befehl erstellt eine Tabelle mit den Spalten id (Primärschlüssel), name und salary.

Erstellen Sie als Nächstes die Tabelle salary_changes, um den Verlauf der Gehaltsänderungen zu speichern:

CREATE TABLE salary_changes (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER,
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2),
    change_date TIMESTAMP
);

Schreiben wir nun die Trigger-Funktion mit PL/pgSQL. Diese Funktion wird vor einer Aktualisierungsoperation (Update Operation) in der Tabelle employees ausgeführt. Sie speichert die alten und neuen Gehaltswerte in der Tabelle salary_changes.

CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO salary_changes (employee_id, old_salary, new_salary, change_date)
        VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Dieser Code definiert eine Funktion namens log_salary_change. Sie prüft, ob sich das Gehalt geändert hat. Wenn dies der Fall ist, fügt sie eine neue Zeile in die Tabelle salary_changes mit der Mitarbeiter-ID (employee ID), dem alten Gehalt (old salary), dem neuen Gehalt (new salary) und dem aktuellen Zeitstempel (timestamp) ein.

Den Trigger erstellen

In diesem Schritt binden Sie die Funktion log_salary_change an die Tabelle employees, sodass sie automatisch ausgeführt wird, wenn eine Gehaltsaktualisierung (salary update) erfolgt.

Erstellen Sie den Trigger mit dem folgenden SQL-Befehl:

CREATE TRIGGER salary_change_trigger
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_change();

Dieser Befehl erstellt einen Trigger namens salary_change_trigger. Er legt fest, dass der Trigger vor einer UPDATE-Operation in der Tabelle employees aktiviert werden soll. Die Klausel FOR EACH ROW gibt an, dass die Trigger-Funktion für jede aktualisierte Zeile ausgeführt werden soll.

Den Trigger testen

In diesem Schritt testen Sie das Verhalten des Triggers. Sie fügen Daten in die Tabelle employees ein und aktualisieren dann das Gehalt, um zu sehen, ob der Trigger die Änderungen korrekt in der Tabelle salary_changes protokolliert.

Fügen Sie zunächst einige Anfangsdaten in die Tabelle employees ein:

INSERT INTO employees (name, salary) VALUES ('Alice', 50000.00);
INSERT INTO employees (name, salary) VALUES ('Bob', 60000.00);

Aktualisieren Sie nun Alices Gehalt:

UPDATE employees SET salary = 55000.00 WHERE name = 'Alice';

Um zu überprüfen, ob der Trigger korrekt funktioniert hat, fragen Sie die Tabelle salary_changes ab:

SELECT * FROM salary_changes;

Sie sollten eine Zeile in der Tabelle salary_changes mit den Informationen über Alices Gehaltsänderung sehen.

Aktualisieren Sie als Nächstes Bobs Gehalt auf denselben Wert, den es bereits hat:

UPDATE employees SET salary = 60000.00 WHERE name = 'Bob';

Da sich das Gehalt nicht tatsächlich ändert, sollte der Trigger keine neue Zeile in die Tabelle salary_changes einfügen. Lassen Sie uns dies überprüfen:

SELECT * FROM salary_changes;

Sie sollten immer noch nur die eine Zeile im Zusammenhang mit Alices Gehaltsänderung sehen.

Fügen Sie abschließend einen neuen Mitarbeiter hinzu:

INSERT INTO employees (name, salary) VALUES ('Charlie', 70000.00);

Das Einfügen eines neuen Mitarbeiters sollte den log_salary_change-Trigger nicht auslösen, da er nur für UPDATE-Ereignisse (events) ausgelöst werden soll. Bestätigen wir dies, indem wir die Tabelle salary_changes erneut überprüfen:

SELECT * FROM salary_changes;

Sie sollten immer noch nur die eine Zeile im Zusammenhang mit Alices Gehaltsänderung sehen.

Den Trigger deaktivieren und löschen

In diesem Schritt lernen Sie, wie Sie einen Trigger in PostgreSQL deaktivieren und löschen.

Deaktivieren Sie zunächst den salary_change_trigger:

ALTER TABLE employees DISABLE TRIGGER salary_change_trigger;

Aktualisieren Sie nun Alices Gehalt erneut:

UPDATE employees SET salary = 60000.00 WHERE name = 'Alice';

Überprüfen Sie die Tabelle salary_changes:

SELECT * FROM salary_changes;

Sie sollten immer noch nur die eine Zeile aus der vorherigen Aktualisierung sehen. Dies bestätigt, dass der Trigger deaktiviert ist.

Um den Trigger wieder zu aktivieren, können Sie den folgenden Befehl verwenden:

ALTER TABLE employees ENABLE TRIGGER salary_change_trigger;

Um den Trigger schließlich dauerhaft zu entfernen, löschen Sie ihn mit dem folgenden Befehl:

DROP TRIGGER salary_change_trigger ON employees;

Zusammenfassung

In diesem Lab haben Sie gelernt, wie Sie Aufgaben in PostgreSQL mithilfe von Triggern automatisieren können. Sie haben eine Trigger-Funktion mit PL/pgSQL erstellt, um Gehaltsänderungen in einer employees-Tabelle in einer salary_changes-Tabelle zu protokollieren. Sie haben die Funktion geschrieben, sie an ein Tabellenereignis (table event) gebunden, sie getestet und sie deaktiviert/gelöscht.