mysql.createTriggerWithCursor

DELIMITER //
/* Trigger for INSERT Dynamische sql wird in Triggern und Functionen nicht unterstützt nur in Prozeduren!!
CURSOR wird nur in Prozeduren und Funktionen definiert!
*/
CREATE TRIGGER tr_after_insert_tblPackstuecke AFTER INSERT ON tblPackstuecke FOR EACH ROW BEGIN
DECLARE col_name, is_primary VARCHAR(20);
DECLARE done INT DEFAULT 0;
DECLARE tabelle VARCHAR(20) DEFAULT 'tblPackstuecke';
DECLARE col_names CURSOR FOR
SELECT column_name,column_key
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = tabelle
ORDER BY ordinal_position;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN col_names;
the_loop: LOOP
IF done=1 THEN
CLOSE col_names;
LEAVE the_loop;
END IF;
FETCH col_names
INTO col_name, is_primary;
IF is_primary='PRI' THEN
INSERT INTO tblLOG (IDPerson, Tabelle, Spalte, Referenz, Alt, Neu)
VALUES (NEW.IDPerson, tabelle, 'Erstellt', 'test', 'NEU', 'NEU');
ELSE
INSERT INTO tblLOG (IDPerson, Tabelle, Spalte, Referenz, Alt, Neu)
VALUES (NEW.IDPerson, tabelle, col_name, NEW.IDPackstueck , NEW.IDPackstueck , NEW.IDPackstueck);
END IF;
END LOOP the_loop;
END; //
DELIMITER ;