mysql触发器同步变更

1.由于表B中没有更新和修改字段；需要将表B的变化反应到表A中。想到了使用触发器

使用数据库及版本：mysql 5.6.15

1.示例表

CREATE TABLE `kecheng` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `jieshu` varchar(20) DEFAULT NULL,
  `kechengming` varchar(40) DEFAULT NULL,
  `weekday` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8

相应表：

create table kecheng_copy like kecheng;
insert into kecheng_copy select * from kecheng;

 

更新触发器：

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `test`.`Test` BEFORE UPDATE
    ON `test`.`kecheng`
    FOR EACH ROW BEGIN
        UPDATE 
  `test`.`kecheng_copy` 
SET
  `jieshu` = new.`jieshu`,
  `kechengming` = new.`kechengming`,
  `weekday` = new.`weekday` 
WHERE `id` = new.`id` ;

    END$$

DELIMITER ;

删除触发器：

DELIMITER $$

USE `test`$$

DROP TRIGGER /*!50032 IF EXISTS */ `TestDELETE`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `TestDELETE` BEFORE DELETE ON `kecheng` 
    FOR EACH ROW BEGIN
        DELETE FROM  
  `test`.`kecheng_copy` 
WHERE `id` = old.`id` ;
    END;
$$

DELIMITER ;