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 ;