1、
#after insert
DELIMITER $$
USE `databasename`$$
DROP TRIGGER /*!50032 IF EXISTS */ `trigger_after_insert_trigger_name`$$
CREATE
/*!50017 DEFINER = 'root'@'%' */
TRIGGER `trigger_after_insert_trigger_name` AFTER INSERT ON `table_name`
FOR EACH ROW BEGIN
INSERT INTO username.`table_name`
VALUES(new.column1,new.column2,new.column3,new.column4,new.column5,new.column6,new.column7,new.column8);
END;
$$
DELIMITER ;2、
#before insert
DELIMITER $$
USE `test`$$
DROP TRIGGER /*!50032 IF EXISTS */ `t_beforeinsert_1`$$
CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `t_beforeinsert_1` BEFORE INSERT ON `test1`
FOR EACH ROW BEGIN
DECLARE insertlog VARCHAR(25) DEFAULT '向test1插入：';
DECLARE chang INT DEFAULT new.id;
SET insertlog=CONCAT(insertlog,chang);
SET NAMES gbk;
INSERT INTO `test`.`test3` VALUES(insertlog);
INSERT INTO `test`.`test2` VALUES(new.id);
END;
$$
DELIMITER ;3、
#after update
DELIMITER $$
USE `test`$$
DROP TRIGGER /*!50032 IF EXISTS */ `t_afterupdate_1`$$
CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `t_afterupdate_1` AFTER UPDATE ON `test1`
FOR EACH ROW BEGIN
/*old表示修改之前的值，new表示修改之后的值*/
UPDATE test.`test2` t1 INNER JOIN `test`.`test1` t2 ON t1.id=old.id SET t1.id=new.id;
END;
$$
DELIMITER ;4、
#before update
DELIMITER $$
USE test $$
DROP TRIGGER IF EXISTS `test`.`t_beforeupdate_1`$$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `test`.`t_beforeupdate_1` BEFORE UPDATE
ON `test`.`test1`
FOR EACH ROW BEGIN
DECLARE insertlog VARCHAR(25) DEFAULT 'update table ';
DECLARE chang INT DEFAULT new.id;
SET insertlog=CONCAT(insertlog,old.id,' to ',chang);
SET NAMES gbk;
INSERT INTO `test`.`test3` VALUES(insertlog);
UPDATE `test`.`test2` t1 INNER JOIN `test`.`test2` t2 ON t1.id=old.id SET t1.id=new.id;
END$$
DELIMITER ;5、
#after delete
DELIMITER $$
USE test $$
DROP TRIGGER IF EXISTS `test`.`t_afterdelete_1` $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `test`.`t_afterdelete_1` AFTER DELETE
ON `test`.`test1`
FOR EACH ROW BEGIN
DELETE FROM test.`test2` WHERE id=old.id;
END;
$$
DELIMITER ;6、
#before delete
DELIMITER $$
USE `test`$$
DROP TRIGGER /*!50032 IF EXISTS */ `t_beforedelete_1`$$
CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `t_beforedelete_1` BEFORE DELETE ON `test1`
FOR EACH ROW BEGIN
DECLARE delLog VARCHAR(25) DEFAULT 'delete test2 id ';
DECLARE val INT DEFAULT old.id;
DECLARE nowdate DATE DEFAULT CURDATE();
SET delLog=CONCAT(delLog,old.id);
INSERT INTO `test`.`test3` VALUES(delLog,nowdate);
DELETE FROM `test`.`test2` WHERE id=old.id;
END;
$$
DELIMITER ;
