Mysql触发器

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 BEGINDECLARE 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 BEGINDECLARE 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 ;