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