mysql实时触发_MYSQL实时触发SQL SERVER

大家大多能够找到SQL SERVER触发后改变MYSQL的表，这个实现起来比较简单，只要sql server写好触发器来实现。这边要说的当MYSQL的一些表发生了增删改的情况下，如何接近实时的去触发来更新sql server的对应表，这边我借鉴了网上这篇文档做了些改进。

http://www.searchdatabase.com.cn/showcontent_39889.htm

和上面的图也有类似的操作过程，唯一不同的是我这边在mysql定义了三个触发器来更新一个每个表的备份表，把修改日期，是插入还是删除的行为状态以及是否完成作为字段写在该表，如下图：

这边sugarcrmtable是原表，而sugardcrmtablebackup是用来记录状态和做过哪些操作的。I代表插入，U代表更新，D代表删除，而executingstate为d代表已经操作。然后在sql server里写好存储过程用作业定时去跑。

USE [SugarCRMDB]

GO

/****** Object: StoredProcedure [dbo].[select_sugarcrmtablebackup] Script Date: 2015/6/18 17:41:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER procedure [dbo].[select_sugarcrmtablebackup]

as

--insert into sugarcrmtablebackup(id,name) select * from openquery(MySql, 'select id,name from sugarcrmtablebackup where NOT ISNULL(ExecutingState) ')

--插入BehaviorState为I的记录

insert into sugarcrmtablebackup(id,name) select * from openquery(MySql, 'select id,name from sugarcrmtablebackup where ISNULL(ExecutingState) and BehaviorType=''i''')

update openquery (MySql, 'select id,name,ExecutingState,ModificationTime from sugarcrmtablebackup where ISNULL(ExecutingState) and BehaviorType=''i''') set ExecutingState='D',ModificationTime=getdate()

--删除BehaviorState为U的记录

if object_id('tempdb..#temp') is not null Begin

drop table #temp

End

select * into #temp from openquery(MySql, 'select id,name from sugarcrmtablebackup where ISNULL(ExecutingState) and BehaviorType=''D''')

delete from sugarcrmtablebackup where id in (select id from #temp)

update openquery (MySql, 'select id,name,ExecutingState,ModificationTime from sugarcrmtablebackup where ISNULL(ExecutingState) and BehaviorType=''D''') set ExecutingState='D',ModificationTime=getdate()

--更新BehaviorState为D的记录

if object_id('tempdb..#temp1') is not null Begin

drop table #temp1

End

select * into #temp1 from openquery(MySql, 'select id,name from sugarcrmtablebackup where ISNULL(ExecutingState) and BehaviorType=''U''')

update sugarcrmtablebackup set id=id,name=name select id,name from #temp1

update openquery (MySql, 'select id,name,ExecutingState,ModificationTime from sugarcrmtablebackup where ISNULL(ExecutingState) and BehaviorType=''U''') set ExecutingState='D',ModificationTime=getdate()

定时器好像我这边只能十秒执行一次，但是我看下来速度也接近于实时的了，如下图：

除此之外，这边还有mysql需要写的三个触发器：

插入触发器

delimiter ||

DROP TRIGGER IF EXISTS t_afterinsert_on_tab1 ||

CREATE TRIGGER t_afterinsert_on_tab1

AFTER INSERT ON sugarcrmtable

FOR EACH ROW

BEGIN

insert into sugarcrmtablebackup(id,name,BehaviorType) values(new.id,new.name,‘I’);

END||

delimiter ;

更新触发器

delimiter ||

DROP TRIGGER IF EXISTS t_afterdelete_on_tab1 ||

CREATE TRIGGER t_afterdelete_on_tab1

AFTER update ON sugarcrmtable

FOR EACH ROW

BEGIN

insert into sugarcrmtablebackup(id,name,BehaviorType) values(new.id,new.name,‘U’);

END||

delimiter ;

删除触发器

delimiter ||

DROP TRIGGER IF EXISTS t_afterdelete_on_tab1 ||

CREATE TRIGGER t_afterdelete_on_tab1

AFTER delete ON sugarcrmtable

FOR EACH ROW

BEGIN

insert into sugarcrmtablebackup(id,name,BehaviorType) values(old.id,old.name,‘D’);

END||

delimiter ;