大家大多能够找到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 ;