mysql 触发器 记录日志_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$