Trigger mySQL, Verständnisfrage

[hotti]

hi,

der geht:

  
CREATE TRIGGER ins_user BEFORE UPDATE ON user  
FOR EACH ROW INSERT INTO test(text, datum)VALUES('trigger', CURRENT_TIMESTAMP())  

anstelle BEFORE müsste doch auch AFTER funktionieren oder hab ich da was übersehen? Bei mir tut sich mit AFTER nämlich nichts ;)

my Version: 5.1.40-community

Bitte mal um Hinweise,
Hotti

(Oder hauts mir um die Ohren)

[Matti Mäkitalo]

Hi,

CREATE TRIGGER ins_user BEFORE UPDATE ON user
FOR EACH ROW INSERT INTO test(text, datum)VALUES('trigger', CURRENT_TIMESTAMP())

>   
> anstelle BEFORE müsste doch auch AFTER funktionieren oder hab ich da was übersehen? Bei mir tut sich mit AFTER nämlich nichts ;)  
  
Sollte funktionieren. Gib mal eine Folge SQL-Statements für C&P an, mit denen man testen kann.  
  
Bis die Tage,  
Matti

-- 
[Webapplikationen in C++ entwickeln](http://tntnet.org/)

[hotti]

hi, danke!

Sollte funktionieren. Gib mal eine Folge SQL-Statements für C&P an, mit denen man testen kann.

Ja, ich hab was vergessen, zu erwähnen, das Statement:

  
	INSERT INTO user(user_name, user_id)  
	VALUES(?,?)  
	ON DUPLICATE KEY UPDATE user_name=?  

wird über ein Script ausgeführt. Wenn ich ein Update 'von Hand' eingebe, funktioniert sowohl BEFORE als auch AFTER im Trigger. Über das Script mit o.g. Statement tut der Trigger nur mit BEFORE. Das ist nicht weiter tragisch, ist sowieso noch nicht fertig ;)

Es wird noch Einiges zu Lesen geben und dann werde ich erst wieder richtig Freude mit Triggern haben, womit ich schon lange nichts mehr gemacht habe.

viele Grüße,
Hotti

[Matti Mäkitalo]

Hi,

Ja, ich hab was vergessen, zu erwähnen, das Statement:

INSERT INTO user(user_name, user_id)
VALUES(?,?)
ON DUPLICATE KEY UPDATE user_name=?

  
`ON DUPLICATE KEY UPDATE`{:.language-sql} macht hier den Unterschied. Je nachdem, ob der Constraint zuschlägt, wird ggf. der AFTER UPDATE-Trigger ausgeführt, und nicht der AFTER INSERT.  
  
Zitat aus [der MySQL-Dokumentation](http://dev.mysql.com/doc/refman/5.1/de/create-trigger.html):  
"Es ist wichtig, zu verstehen, dass das trigger\_event weniger eine Art von SQL-Anweisung ist, die den Trigger aktiviert, als vielmehr eine Art von Tabellenoperation. So wird beispielsweise ein INSERT-Trigger nicht nur von INSERT-Anweisungen, sondern auch von LOAD DATA-Anweisungen aktiviert, weil beide Anweisungen Zeilen in eine Tabelle einfügen.  
Ein potenziell verwirrendes Beispiel dafür ist die Syntax von INSERT INTO ... ON DUPLICATE KEY UPDATE ...: Für jede Zeile wird ein BEFORE INSERT-Trigger aktiviert, gefolgt entweder von einem AFTER INSERT-Trigger oder von dem Triggerpaar aus BEFORE UPDATE und AFTER UPDATE, je nachdem, ob ein doppelter Schlüssel für die Zeile vorlag oder nicht."  
Bis die Tage,  
Matti

-- 
[Webapplikationen in C++ entwickeln](http://tntnet.org/)

[hotti]

hi Matti,

Zitat aus der MySQL-Dokumentation:
"Es ist wichtig, zu verstehen, dass das trigger_event weniger eine Art von SQL-Anweisung ist, die den Trigger aktiviert, als vielmehr eine Art von Tabellenoperation. So wird beispielsweise ein INSERT-Trigger nicht nur von INSERT-Anweisungen, sondern auch von LOAD DATA-Anweisungen aktiviert, weil beide Anweisungen Zeilen in eine Tabelle einfügen.
Ein potenziell verwirrendes Beispiel dafür ist die Syntax von INSERT INTO ... ON DUPLICATE KEY UPDATE ...: Für jede Zeile wird ein BEFORE INSERT-Trigger aktiviert, gefolgt entweder von einem AFTER INSERT-Trigger oder von dem Triggerpaar aus BEFORE UPDATE und AFTER UPDATE, je nachdem, ob ein doppelter Schlüssel für die Zeile vorlag oder nicht."

Ja, das habe ich gestern abend auch noch gelesen, das konfusioniert ;)
Wie auch immer, Trigger sind eine feine Sache, ich habe damit vor ein paar Jahren mal was gemacht, allerdings nicht mit MySQL sondern mit PG.

Mir gehts in der nächsten Sache darum, zu erkennen, ob Update's stattgefunden haben, was im darüberliegenden Frontend so nicht gleich erkennbar ist. Der Trigger wird mir genau das schön brav in eine Tabelle schreiben, die der Anwender dann gezielt abfragen kann.

Viele Grüße,
Hotti

[hotti]

hi again,

Ein potenziell verwirrendes Beispiel dafür ist die Syntax von INSERT INTO ... ON DUPLICATE KEY UPDATE ...: Für jede Zeile wird ein BEFORE INSERT-Trigger aktiviert, gefolgt entweder von einem AFTER INSERT-Trigger oder von dem Triggerpaar aus BEFORE UPDATE und AFTER UPDATE, je nachdem, ob ein doppelter Schlüssel für die Zeile vorlag oder nicht."

Getestet:
Wenn Insert getriggert werden soll: Trigger ... After Insert
Wenn Update getriggert werden soll: Trigger ... Before Update
=> getriggert wird nur, wenn ein Duplicate Key vorliegt.

Wenn generell ein Insert getriggert werden soll: Trigger ... Before Insert

Meine Güte, hier heißt es aber aufgepasst ;)

Hotti

[Matti Mäkitalo]

Hi,

Ein potenziell verwirrendes Beispiel dafür ist die Syntax von INSERT INTO ... ON DUPLICATE KEY UPDATE ...: Für jede Zeile wird ein BEFORE INSERT-Trigger aktiviert, gefolgt entweder von einem AFTER INSERT-Trigger oder von dem Triggerpaar aus BEFORE UPDATE und AFTER UPDATE, je nachdem, ob ein doppelter Schlüssel für die Zeile vorlag oder nicht."

Wenn Insert getriggert werden soll: Trigger ... After Insert
Wenn Update getriggert werden soll: Trigger ... Before Update

Das ist nicht vollständig, und das sagt der Text auch aus. Schau dir mal das Ergebnis hier an:

DROP TABLE IF EXISTS testtable;  
CREATE TABLE testtable  
(  
    id INTEGER AUTO_INCREMENT,  
    ival INTEGER NOT NULL DEFAULT 0,  
    PRIMARY KEY(id)  
 );  
  
DROP TABLE IF EXISTS testtable_log;  
CREATE TABLE testtable_log  
(  
    id INTEGER AUTO_INCREMENT,  
    ival INTEGER NOT NULL DEFAULT 0,  
    trigger_name VARCHAR(20),  
    PRIMARY KEY(id)  
 );  
  
DELIMITER |  
  
CREATE TRIGGER BeforeInsertTestTable BEFORE INSERT ON testtable FOR EACH ROW  
  INSERT INTO testtable_log (ival, trigger_name) VALUES (NEW.ival, 'BEFORE INSERT'); |  
  
CREATE TRIGGER AfterInsertTestTable AFTER INSERT ON testtable FOR EACH ROW  
  INSERT INTO testtable_log (ival, trigger_name) VALUES (NEW.ival, 'AFTER INSERT'); |  
  
CREATE TRIGGER BeforeUpdateTestTable BEFORE UPDATE ON testtable FOR EACH ROW  
  INSERT INTO testtable_log (ival, trigger_name) VALUES (NEW.ival, 'BEFORE UPDATE'); |  
  
CREATE TRIGGER AfterUpdateTestTable AFTER UPDATE ON testtable FOR EACH ROW  
  INSERT INTO testtable_log (ival, trigger_name) VALUES (NEW.ival, 'AFTER UPDATE'); |  
  
DELIMITER ;  
INSERT INTO testtable (id, ival) VALUES (1, 1) ON DUPLICATE KEY UPDATE ival = ival+1;  
INSERT INTO testtable (id, ival) VALUES (1, 1) ON DUPLICATE KEY UPDATE ival = ival+1;  
SELECT * FROM testtable_log;

Das Ergebnis ist
id, ival, trigger_name
1,  1,    BEFORE INSERT
2,  1,    AFTER INSERT
3,  1,    BEFORE INSERT
4,  2,    BEFORE UPDATE
5,  2,    AFTER UPDATE

Zeilen 1 und 2 kommen vom ersten INSERT-Statement, Zeilen 3, 4 und 5 vom zweiten.
Beachte, dass NEW.* beim BEFORE-INSERT-Trigger noch den Wert enthält, der im INSERT-Statement angegeben wurde. Dann wurde festgestellt, dass der Primary-Key-Constraint greift und das Trigger-Paar BEFORE/UPDATE-Trigger wird aufgerufen, diesmal mit den aktualisierten Wert.

Bis die Tage,
Matti

[hotti]

hi,

Das ist nicht vollständig, und das sagt der Text auch aus.

Richtig. Für meinen konkreten Fall brauche ich jedoch nur zwei Trigger, die habe ich gestern aufgeschrieben, getestet und damit auch nachvollzeiehen können, was in der Dokumentation steht. Die anderen Fälle habe ich natürlich auch mal durchgespielt ;)

Entscheidend ist, dass mit der richtigen Schlüssel-Setzung ein Insert/Update funktioniert und zwar so, dass das mit einem einzigen Statement erledigt werden kann, ohne dass dazu ein eigener Code geschrieben werden muss. Dann ist es auch nicht schwer, einen dem Ereignis entsprechenden Trigger zu schreiben.

Viele Grüße,
Hotti