30
Aug

Trigger in MySQL

categories Computer, MySQL, Tutorials    

Aufgabenstellung:
Wir benötigen zwei Zeitstempel innerhalb einer Tabelle mit einigen darauf verweisenden Tabellen. Der eine Zeitstempel soll das Anlagedatum des Datensatz darstellen, der zweite das „zuletzt aktualisiert“-Datum. Schön wäre, wenn die Tabelle diese Felder automatisch setzen würde.

Hierfür baue ich mir erstmal eine Testtabelle:

CREATE TABLE `t1` (                                               
   `kundennummer` int(11) NOT NULL,                                    
   `testfeld1` int(11) NOT NULL,                                    
   `anlage` timestamp NOT NULL default '0000-00-00 00:00:00',      
   `update` timestamp NOT NULL default '0000-00-00 00:00:00',      
   PRIMARY KEY  (`kundennummer`)                                       
) ENGINE=MyISAM DEFAULT;

In MySQL kann man ein timestamp-Feld mit zwei verschiedenen Optionen belegen, welche man auch kombinieren kann:

Prima, genau das, was ich für die Anforderung benötige!

Die Sache hat leider nur einen kleinen Haken: Je Tabelle darf nur ein einziges timestamp-Feld mit diesen Optionen belegt sein. Wie löse ich nun die Aufgabenstellung?

Die Antwort lautet: Trigger.

Ein Trigger ist ein Eventhandler für Tabellen (dies ist meine persönliche Definition, sicher gibt es wesentlich treffendere Bezeichnungen). Drei Events können getriggert werden: INSERT, UPDATE und DELETE (bitte hier nicht gleichsetzen mit den gleichlautenden SQL-Kommandos, z.B. fällt unter einem INSERT z.B. auch ein LOAD DATA u.s.w.). Man kann frei wählen, ob die gewünschte Aktion vor oder nach dem Event ausgeführt werden soll. Damit liefert uns die Datenbank ein Feature, mit welchem – soviel fand ich nach meinen ersten Spielereien mit der Funktion Trigger selbst heraus – extrem viele lustige Dinge anstellen.

So sieht mein Trigger für das Setzen des Neuanlagedatums aus:

CREATE TRIGGER  t1_neuanlage_timestamp 
BEFORE INSERT on t1
FOR EACH ROW 
SET NEW.anlage = NOW();

Im Klartext bedeutet dies, dass jedes Insert-Event (sei es denn durch einen Dump oder einem SQL-Insert) dazu führt, das Feld „anlage“ des neuen Datensatz mit dem aktuellen Datum zu versehen.

Gleiches geht natürlich auf für Setzen des Aktualisierungsdatum:

CREATE TRIGGER  t1_update_timestamp 
BEFORE UPDATE on t1
FOR EACH ROW 
SET NEW.update = NOW();

Gleiches Prinzip, nahezu gleiche Wirkung – funktioniert reibungslos! Damit ist die Anforderung sauber umgesetzt.

Hier noch ein einfaches Szenario, wie man Trigger etwas weitergehend verwenden kann:
Angenommen, für o.a. Tabelle gibt es eine zweite Tabelle mit direkter Abhängigkeit. Angenommen, man möchte bei Änderungen in Tabelle 2 die Aktualisierung in Tabelle 1 setzen. Kein Problem mit Trigger. Seht selbst…

 
CREATE TABLE `t2` (                                               
   `kundennummer` int(11) NOT NULL,                                    
   `testfeld2` int(11) NOT NULL,                                    
   PRIMARY KEY  (`kundennummer`)                                       
) ENGINE=MyISAM DEFAULT;

CREATE TRIGGER t2_update_timestamp 
BEFORE UPDATE on t2 
FOR EACH ROW 
UPDATE t1 
SET t1.update = NOW() 
WHERE t1.kundennummer = NEW.kundennummer;

Fertig!

Einschränkungen gibt es zwar auch für Trigger in MySQL, jedoch sind diese recht überschaubar. Trigger sind sehr gut dokumentiert, sodass man sicherlich die meisten Fragestellungen – wie so oft – im Handbuch klären kann!

Kommentare

Comments (6) - “Trigger in MySQL”

  1. Schäöpf Rene am 11.05.2009 um %H:%M Uhr 

    Diese Seite ist sehr hilfreich! meine tochter hat dabei viel gelernt und gesagt dass diese seite besser ist als ihr professor! danke Björn!

    fuz

  2. Jonas am 16.11.2009 um %H:%M Uhr 

    Vielen dank für diese kurze Übersicht, hat mir sehr geholfen!

  3. Jan 'luckyduck' Brinkmann am 14.06.2010 um %H:%M Uhr 

    Ergänzend wäre vllt. in der netten Übersicht für den Einstieg noch super, wenn erwähnt wird wie man sich bereits angelegte Trigger anschauen kann (show triggers).

  4. Björn am 15.06.2010 um %H:%M Uhr 

    Falls ich mich mal wieder in das Thema Trigger stürze, werde ich den Beitrag ein wenig aktualisieren und ergänzen. Es gibt noch so viele Dinge, die man zu Trigger sagen kann! 😉

  5. Thomas am 25.03.2014 um %H:%M Uhr 

    Was passiert eigentlich, wenn die Rohdaten auch als Timestamp daher kommen?

    Kurzes Scenario:
    1. aus einem Backend heraus wird der Timestamp regulär gesetzt
    2. aus einem erstellten Formular (Formulargenerator) können die Daten nur als String weiter gereicht werden

    Vor diesem Problem stehe ich gerade. 🙁

    Wenn ich das richtig verstanden habe, wird der Trigger immer ausgeführt, z.B. bei BEFORE würde der Timestamp aus dem Backend nochmals berechnet und ein falsches Ergebnis liefern. Wenn es anders sein sollte, hätte ich eine Lösung, ohne über PHP gehen zu müssen.

    Vielleicht kann mir da einer nähere Infos geben? 😉

  6. Björn am 25.03.2014 um %H:%M Uhr 

    Hallo Thomas,

    falls Du mein Beispiel adaptierst, den Trigger mit BEFORE setzt, und das Feld im INSERT mit inkludierst, wird es zuerst durch den Trigger verändert, dann durch den INSERT auf den Wert, den Du mitgibst. Wenn ich Dein Szenario richtig verstanden habe, stellt sich damit eigentlich das Problem nicht – quasi doppelt gemoppelt.

    Der Timestamp in meinem Post soll auch prinzipiell nur veranschaulichen, wie ein Trigger grundsätzlich funktioniert. Wenn Du Formulardaten in Deiner Tabelle speichern willst, dann eignet sich ein Trigger auf einem der Formularfelder zumeist nur bedingt. Ich würde einen Trigger immer nur da setzen wollen, wo ich ihn wirklich benötige. Das Beispiel eines zuletzt-aktualisiert-Feldes ist hierfür sehr plakativ und fällt mir immer zuerst ein, wenn es um solche Themen geht (ich glaube nicht, dass ich via Formular so ein Feld verändern lasse…).

    Grüße,
    Björn

Leave a comment!