MySQL 触发器简单实例

/*取消外键约束*/
SET FOREIGN_KEY_CHECKS=0;

/*创建C1表 主键ID 字段name*/
DROP TABLE IF EXISTS `c1`;
CREATE TABLE `c1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(120) CHARACTER SET utf8mb4 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8;

/*C2与C1结构相同 去掉了ID自增*/
DROP TABLE IF EXISTS `c2`;
CREATE TABLE `c2` (
  `id` int(11) NOT NULL,
  `name` varchar(120) CHARACTER SET utf8mb4 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8;

/*给C1创建一个触发器 名称叫c1-insert*//*触发条件是 AFTER INSERT*/
DROP TRIGGER IF EXISTS `c1-insert`;
DELIMITER ;;
CREATE TRIGGER `c1-insert` AFTER INSERT ON `c1` FOR EACH ROW BEGIN 
/*插入后的ID*/
SET @id = NEW.id; 
/*插入后的name*/
SET @name = NEW.name ;
INSERT INTO `c2` (`id`, `name`) VALUES (@id,@name); /*插入到C2表*/
END
;;


/*给C1创建一个触发器 名称叫c1-update*//*触发条件是 AFTER UPDATE*/
DELIMITER ;
DROP TRIGGER IF EXISTS `c1-update`;
DELIMITER ;;
CREATE TRIGGER `c1-update` AFTER UPDATE ON `c1` FOR EACH ROW BEGIN.
/*修改前的ID*/
SET @id = OLD.id; 
/*修改后的name*/
SET @name = NEW.name;
UPDATE `c2` SET `name`=@name WHERE id = @id; /*更新到C2表*/
END
;;