MySql触发器，可用于小型项目日志记录、数据同步

触发器支持跨库触发！！！！！！！！！！！

创建表一，原始表省级

CREATE TABLE `hat_province` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `provinceID` varchar(6) DEFAULT NULL COMMENT '省级code',
  `province` varchar(40) DEFAULT NULL COMMENT '省级名称',
  `area` varchar(20) DEFAULT NULL COMMENT '所属区域名称',
  `group_id` int(6) DEFAULT NULL COMMENT '所属区域编号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8 COMMENT='省级表';

创建表二，同步表

DROP TABLE IF EXISTS `t_link_hat_province`;
CREATE TABLE `t_link_hat_province` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `provinceID` varchar(6) DEFAULT NULL COMMENT '省级code',
  `province` varchar(40) DEFAULT NULL COMMENT '省级名称',
  `area` varchar(20) DEFAULT NULL COMMENT '所属区域名称',
  `group_id` int(6) DEFAULT NULL COMMENT '所属区域编号',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=46 DEFAULT CHARSET=utf8 COMMENT='地址省';

创建insert 同步触发器

-- 触发器存在则删除
DROP TRIGGER
IF EXISTS insert_t_link_hat_province ; 

-- 创建添加同步
CREATE TRIGGER insert_t_link_hat_province AFTER INSERT ON hat_province FOR EACH ROW
BEGIN

IF NOT EXISTS (
	SELECT
		*
	FROM
		t_link_hat_province
	WHERE
		id = NEW.id
) THEN
	INSERT INTO t_link_hat_province (
		`id`,
		`provinceID`,
		`province`,
		`area`,
		`group_id`
	)
VALUES
	(
		NEW.id,
		NEW.provinceID,
		NEW.province,
		NEW.area,
		NEW.group_id
	) ;
END
IF;  END$

创建update 触发器

DROP TRIGGER
IF EXISTS update_t_link_hat_province ; 

CREATE TRIGGER update_t_link_hat_province AFTER UPDATE ON hat_province FOR EACH ROW
BEGIN
	UPDATE t_link_hat_province
		SET `provinceID` = NEW.provinceID,
		 `province` = NEW.province,
		 `area` = NEW.area,
		 `group_id` = NEW.group_id
		WHERE
	id = NEW.id ; END$

创建delete 触发器

DROP TRIGGER
IF EXISTS del_t_link_hat_province ; 
CREATE TRIGGER del_t_link_hat_province AFTER DELETE ON hat_province FOR EACH ROW
BEGIN
	DELETE
FROM
	t_link_hat_province
WHERE
	id = OLD.id ;
END$