MySQL触发器

触发器作用：是一种特殊的存储过程。
当当前表的数据发生改变的时候，会触发某个表的某数据也发生改变。有一种数据同步的样子。
咱们拿案例说话吧：
下面是一个测试表，想要做的是当某个库的这个表的数据发生变化，那么另一个库的这个表的数据也会改变。(前提是在同一个数据库服务器上的2个数据库实例的表)

CREATE TABLE `cc_command_url`
(
	`id` VARCHAR(36) NOT NULL COMMENT '主键 ',
	`title` VARCHAR(200) NULL COMMENT '舆情标题',
	`info_url` VARCHAR(500) NULL COMMENT '舆情URL链接',
	`content` TEXT NULL COMMENT '舆情内容',
	`source_type_name` VARCHAR(100) NULL COMMENT '舆情来源类型名称',
	`website_name` VARCHAR(50) NULL COMMENT '来源网站名称',
	`report_way` CHAR(1) NULL COMMENT '上报渠道：0互联网舆情上报  1内部录入 2舆情发现系统  3舆情监测分析 4态势感知系统 ',
	`reporter` VARCHAR(50) NULL COMMENT '上报人姓名',
	`reporter_tel` VARCHAR(50) NULL COMMENT '上报人联系方式',
	`report_reason` VARCHAR(200) NULL COMMENT '上报原因',
	`report_time` DATETIME NULL COMMENT '上报时间',
	`system_code` VARCHAR(30) NULL COMMENT '系统编号 ',
	CONSTRAINT `PK_cc_command_url` PRIMARY KEY (`id` ASC)
)

新增触发器：

CREATE TRIGGER `cc_command_url_insert` AFTER INSERT ON `cc_command_url` FOR EACH ROW
BEGIN
IF
	@cc_command_url_insert_trigger IS NULL THEN
	SET @cc_command_url_insert_trigger = 1;
INSERT INTO nm_lcs_private.cc_command_url 
( id, cd_id, title, info_url, content, source_type_name, website_name, report_way, reporter, reporter_tel, report_reason, report_time, system_code )
VALUES
	(
		new.id,
		new.cd_id,
		new.title,
		new.info_url,
		new.content,
		new.source_type_name,
		new.website_name,
		new.report_way,
		new.reporter,
		new.reporter_tel,
		new.report_reason,
		new.report_time,
		new.system_code 
	);
END IF;
SET @cc_command_url_insert_trigger = NULL;
END;

修改触发器：

create trigger `cc_command_url_update`
    after insert
    on `cc_command_url`
    for each row
begin
    if @cc_command_url_update_trigger is null then
        set @cc_command_url_update_trigger = 1;
        update nm_lcs_private.cc_command_url b
        set b.title               = new.title,
			b.cd_id               = new.cd_id,
            b.info_url            = new.info_url,
            b.content             = new.content,
            b.source_type_name    = new.source_type_name,
            b.website_name        = new.website_name,
            b.report_way          = new.report_way,
            b.reporter            = new.reporter,
            b.reporter_tel        = new.reporter_tel,
            b.report_reason       = new.report_reason,
            b.report_time         = new.report_time,
            b.system_code         = new.system_code
        where b.id = new.id;
    end if;
    set @cc_command_url_update_trigger = null;
end;

删除触发器：

drop trigger if exists `cc_command_url_delete`;
create trigger `cc_command_url_delete`
    after insert
    on `cc_command_url`
    for each row
begin
    if @cc_command_url_delete_trigger is null then
        set @cc_command_url_delete_trigger = 1;
		delete from nm_lcs_private.cc_command_url where id = old.id;		
    end if;
    set @cc_command_url_delete_trigger = null;
end;

说明：nm_lcs_private是数据库实例，也就是当前库的表的数据变了，nm_lcs_private所对应的表的数据也跟着改变。