SQLite Trigger Automatisierung

Einführung

In diesem Lab werden Sie die SQLite Trigger-Automatisierung erkunden. Sie werden lernen, wie man Trigger erstellt, validiert und verwaltet, die automatisch SQL-Anweisungen als Reaktion auf bestimmte Ereignisse in einer Tabelle ausführen. Dieses Lab führt Sie durch die Erstellung von Audit-Logs und die effektive Verwaltung von Triggern.

Skills Graph

Tabellen erstellen: employees und employee_audit

In diesem Schritt erstellen Sie zwei Tabellen: employees zur Speicherung von Mitarbeiterdaten und employee_audit zur Protokollierung von Gehaltsaktualisierungen.

Öffnen Sie zunächst die SQLite-Shell in Ihrem Terminal.

sqlite3 /home/labex/project/test.db

Dieser Befehl öffnet die SQLite-Shell und verbindet sich mit einer Datenbankdatei namens test.db. Wenn die Datei nicht existiert, wird sie von SQLite erstellt.

Erstellen Sie nun die Tabelle employees mit der folgenden Struktur:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    salary REAL
);

Dieser SQL-Befehl erstellt eine Tabelle namens employees mit drei Spalten:

• id: Eine Ganzzahl, die als Primärschlüssel (primary key) für jeden Mitarbeiter dient.
• name: Ein Textfeld zur Speicherung des Namens des Mitarbeiters.
• salary: Eine reelle Zahl zur Speicherung des Gehalts des Mitarbeiters.

Erstellen Sie als Nächstes die Tabelle employee_audit:

CREATE TABLE employee_audit (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_id INTEGER,
    old_salary REAL,
    new_salary REAL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Diese Tabelle speichert ein Protokoll der Gehaltsänderungen. Die Spalten sind:

• id: Eine eindeutige Kennung für jeden Audit-Eintrag (audit entry), die automatisch inkrementiert wird.
• employee_id: Die ID des Mitarbeiters, dessen Gehalt geändert wurde.
• old_salary: Das Gehalt vor der Aktualisierung.
• new_salary: Das Gehalt nach der Aktualisierung.
• updated_at: Ein Zeitstempel (timestamp), der angibt, wann die Aktualisierung stattgefunden hat.

Beispieldaten in employees einfügen

In diesem Schritt fügen Sie einige Beispieldaten in die Tabelle employees ein. Diese Daten werden verwendet, um den Trigger zu testen, den Sie im nächsten Schritt erstellen werden.

Führen Sie die folgenden SQL-Befehle aus, um drei Mitarbeiterdatensätze einzufügen:

INSERT INTO employees (id, name, salary) VALUES (1, 'Alice', 50000.00);
INSERT INTO employees (id, name, salary) VALUES (2, 'Bob', 60000.00);
INSERT INTO employees (id, name, salary) VALUES (3, 'Charlie', 70000.00);

Diese Befehle fügen der Tabelle employees drei Zeilen mit den angegebenen Werten für id, name und salary hinzu.

Um zu bestätigen, dass die Daten korrekt hinzugefügt wurden, führen Sie diesen Befehl aus, um alle Datensätze in der Tabelle anzuzeigen:

SELECT * FROM employees;

Erwartete Ausgabe:

1|Alice|50000.0
2|Bob|60000.0
3|Charlie|70000.0

Diese Ausgabe zeigt die id, den name und das salary für jeden Datensatz.

Einen Trigger erstellen: salary_update_trigger

Jetzt erstellen Sie einen Trigger, der Gehaltsaktualisierungen automatisch in der Tabelle employee_audit protokolliert.

Führen Sie den folgenden SQL-Befehl aus, um den Trigger zu erstellen:

CREATE TRIGGER salary_update_trigger
AFTER UPDATE OF salary ON employees
BEGIN
    INSERT INTO employee_audit (employee_id, old_salary, new_salary)
    VALUES (OLD.id, OLD.salary, NEW.salary);
END;

Dieser Befehl erstellt einen Trigger namens salary_update_trigger. Dieser Trigger wird automatisch nach jeder Aktualisierung (update) der Spalte salary der Tabelle employees ausgeführt.

• AFTER UPDATE OF salary ON employees: Gibt an, dass der Trigger nach einer Aktualisierung der Spalte salary in der Tabelle employees aktiviert wird.
• BEGIN ... END: Umschließt die SQL-Anweisungen, die ausgeführt werden sollen, wenn der Trigger aktiviert wird.
• INSERT INTO employee_audit ...: Fügt einen neuen Datensatz in die Tabelle employee_audit mit der Mitarbeiter-ID (employee_id), dem alten Gehalt (old_salary) und dem neuen Gehalt (new_salary) ein.
  • OLD.id: Bezieht sich auf die id der Zeile vor der Aktualisierung.
  • OLD.salary: Bezieht sich auf das salary vor der Aktualisierung.
  • NEW.salary: Bezieht sich auf das salary nach der Aktualisierung.

Den Trigger testen

In diesem Schritt testen Sie den Trigger, indem Sie das Gehalt eines Mitarbeiters aktualisieren und dann überprüfen, ob ein neuer Datensatz zur Tabelle employee_audit hinzugefügt wurde.

Aktualisieren wir Bobs Gehalt:

UPDATE employees SET salary = 65000.00 WHERE id = 2;

Dieser Befehl aktualisiert die Spalte salary für den Mitarbeiter mit der id 2 (Bob) auf 65000.00.

Fragen Sie nun die Tabelle employee_audit ab, um zu sehen, ob der Trigger funktioniert hat:

SELECT * FROM employee_audit;

Erwartete Ausgabe:

1|2|60000.0|65000.0|...

Sie sollten eine Zeile in der Tabelle employee_audit mit Bobs Mitarbeiter-ID (employee_id), altem Gehalt (old_salary) und neuem Gehalt (new_salary) sehen. Die Spalte updated_at zeigt den Zeitstempel der Aktualisierung an. Der genaue Zeitstempel variiert je nachdem, wann Sie die Aktualisierung ausgeführt haben.

Den Trigger löschen (Drop)

In diesem Schritt lernen Sie, wie Sie einen Trigger aus Ihrer SQLite-Datenbank löschen oder entfernen.

Um den salary_update_trigger zu löschen, führen Sie den folgenden Befehl aus:

DROP TRIGGER salary_update_trigger;

Dieser Befehl entfernt den salary_update_trigger aus der Datenbank.

Um zu überprüfen, ob der Trigger gelöscht wurde, können Sie die Tabelle sqlite_master abfragen:

SELECT name FROM sqlite_master WHERE type='trigger' AND name='salary_update_trigger';

Diese Abfrage sollte ein leeres Resultset zurückgeben, was darauf hindeutet, dass der Trigger nicht mehr existiert.

Versuchen wir nun, Alices Gehalt zu aktualisieren, um zu bestätigen, dass der Trigger tatsächlich entfernt wurde:

UPDATE employees SET salary = 55000.00 WHERE id = 1;

Überprüfen Sie dann die Tabelle employee_audit:

SELECT * FROM employee_audit;

Sie sollten feststellen, dass kein neuer Eintrag zur Tabelle employee_audit hinzugefügt wurde, was bestätigt, dass der Trigger erfolgreich gelöscht wurde.

Beenden Sie abschließend die SQLite-Shell:

.exit

Zusammenfassung

In diesem Lab haben Sie gelernt, wie man SQLite-Trigger erstellt, die automatisch SQL-Anweisungen als Reaktion auf Aktualisierungsereignisse (Update Events) in einer bestimmten Tabelle ausführen. Sie haben eine employees-Tabelle und eine employee_audit-Tabelle erstellt. Anschließend haben Sie einen Trigger namens salary_update_trigger erstellt, der Gehaltsaktualisierungen in der employee_audit-Tabelle protokolliert. Abschließend haben Sie gelernt, wie man einen Trigger löscht (drop).