create trigger syntax

_mydata.sql

CREATE TABLE `_mydata` (
  `id` int(10) unsigned NOT NULL,
  `version` int(10) unsigned NOT NULL,
  `creation` datetime DEFAULT NULL,
  `data` varchar(100) DEFAULT NULL,
  `action` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`,`version`),
  UNIQUE KEY `id_version` (`id`,`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create_trigger_syntax.sql

CREATE TRIGGER <trigger_name> <BEFORE|AFTER> <INSERT|UPDATE|DELETE>
ON <table_name> FOR EACH ROW <trigger_body>

mydata_after_delete.sql

DELIMITER $$
CREATE
TRIGGER `mydata_after_delete`
AFTER DELETE ON `mydata`
FOR EACH ROW
BEGIN
    DECLARE nextversion int(10) unsigned;
    SELECT (MAX(`version`) + 1) FROM `_mydata` WHERE id = OLD.id into nextversion ;
    INSERT INTO `_mydata` VALUES(OLD.id, nextversion, NOW(), '', 'DELETE');
END
$$

mydata_after_insert.sql

DELIMITER $$
 
CREATE
TRIGGER `mydata_after_insert`
AFTER INSERT ON `mydata`
FOR EACH ROW
BEGIN
    INSERT INTO `_mydata` VALUES(NEW.id, 1, NOW(), NEW.data, 'INSERT');
END;
$$

mydata_after_update.sql

DELIMITER $$
CREATE
TRIGGER `mydata_after_update`
AFTER UPDATE ON `mydata`
FOR EACH ROW
BEGIN
    DECLARE nextversion int(10) unsigned;
    SELECT (MAX(`version`) + 1) FROM `_mydata` WHERE id = NEW.id into nextversion ;
    INSERT INTO `_mydata` VALUES(NEW.id, nextversion, NOW(), NEW.data, 'UPDATE');
END;
$$