Trigger in SQLite – updated_at, das niemand vergessen kann

updated_at ist die Spalte, die in fast jeder Tabelle steht und in fast jedem Projekt irgendwann lügt: Drei Stellen im Code setzen sie, die vierte vergisst es, und der Datenimport vom letzten Monat hat sie nie gekannt. Die Datenbank kann das selbst.

Der Trigger

CREATE TABLE kunden (
  id         INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  email      TEXT,
  created_at INTEGER NOT NULL DEFAULT (unixepoch()),
  updated_at INTEGER NOT NULL DEFAULT (unixepoch())
);

CREATE TRIGGER kunden_updated
AFTER UPDATE ON kunden
FOR EACH ROW
BEGIN
  UPDATE kunden SET updated_at = unixepoch() WHERE id = NEW.id;
END;

Ab jetzt ist der Zeitstempel richtig – egal ob die Anwendung schreibt, ein Migrationsskript oder jemand mit der SQLite-Konsole.

unixepoch() gibt es seit SQLite 3.38; in älteren Versionen nimmst du strftime('%s','now').

Endlosschleifen vermeiden

Der Trigger führt selbst ein UPDATE aus, das denselben Trigger erneut auslösen könnte. SQLite verhindert das standardmäßig, weil rekursive Trigger ausgeschaltet sind. Verlass dich nicht darauf, sondern schreib die Bedingung hin – dann stimmt es auch mit PRAGMA recursive_triggers = ON:

CREATE TRIGGER kunden_updated
AFTER UPDATE ON kunden
FOR EACH ROW
WHEN NEW.updated_at = OLD.updated_at      -- nur, wenn der Stempel nicht schon gesetzt wurde
BEGIN
  UPDATE kunden SET updated_at = unixepoch() WHERE id = NEW.id;
END;

Zweites Beispiel: eine Änderungshistorie

CREATE TABLE preis_historie (
  id        INTEGER PRIMARY KEY,
  artikel   INTEGER NOT NULL,
  alt       INTEGER,
  neu       INTEGER,
  geaendert INTEGER NOT NULL DEFAULT (unixepoch())
);

CREATE TRIGGER artikel_preis_protokoll
AFTER UPDATE OF preis ON artikel
FOR EACH ROW
WHEN OLD.preis IS NOT NEW.preis
BEGIN
  INSERT INTO preis_historie (artikel, alt, neu) VALUES (NEW.id, OLD.preis, NEW.preis);
END;

Zwei Feinheiten: AFTER UPDATE OF preis feuert nur bei Änderungen an dieser Spalte, und IS NOT vergleicht auch dann richtig, wenn einer der Werte NULL ist.

Regeln erzwingen statt nur protokollieren

CREATE TRIGGER lager_nicht_negativ
BEFORE UPDATE OF bestand ON artikel
FOR EACH ROW
WHEN NEW.bestand < 0
BEGIN
  SELECT RAISE(ABORT, 'Bestand darf nicht negativ werden');
END;

RAISE(ABORT, …) bricht die Anweisung mit einer Fehlermeldung ab – die Regel gilt damit für jeden Zugriffsweg, nicht nur für den einen, an den die Anwendung gedacht hat.

Fallstrick

Trigger sind unsichtbar: Sie stehen nicht im Anwendungscode, und wer sqlite3 daten.db ".schema" nicht liest, wundert sich über Werte, die sich von selbst ändern. Dokumentiere sie deshalb dort, wo das Datenmodell beschrieben ist. Und beachte: INSERT OR REPLACE ist intern ein DELETE plus INSERT – dabei feuern die Lösch- und Einfüge-Trigger, nicht der Update-Trigger. Für „einfügen oder aktualisieren" nimm ON CONFLICT DO UPDATE.

Datenmodelle, die ihre Regeln selbst durchsetzen, sind langfristig die günstigeren – ich helfe gern beim Entwurf: bymw.de.

Quellen

• CREATE TRIGGER (sqlite.org)
• Date And Time Functions (sqlite.org)
• PRAGMA recursive_triggers (sqlite.org)