触发器可以记录对表的添加,修改,删除,
这样可以通过触发器,记录一段时间内的表的变动的记录,把这些记录存到一个变动记录表里,
数据同步的时候,就可以读取这张变成的记录表,只需要同步变动过的记录,这样可以大大提高同步的速度.
对原表删除的操作,可以能新表做DELETE操作
对原表进行Insert 或Update操作,可以执行新建或更新操作.
下面是一个触发器(SQL SERVER 2000)的例子:
1
if exists (select * from dbo.sysobjects where id = object_id(N'[risk].[TRG_GUARANTEE_R_IN_HG]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
2drop trigger [risk].[TRG_GUARANTEE_R_IN_HG]
3if exists (select * from dbo.sysobjects where id = object_id(N'[risk].[TRG_GUARANTEE_R_DEL_HG]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
4drop trigger [risk].[TRG_GUARANTEE_R_DEL_HG]
5CREATE TRIGGER TRG_GUARANTEE_R_IN_HG
6ON GUARANTEE_R
7FOR INSERT,UPDATE
8AS
9UPDATE A SET ARJ_MARK='M',CREATE_TIME=GETDATE(),USING_FLAG='0000000000'
10 FROM LOG_ENTRY_INC2 A INNER JOIN INSERTED B ON A.ENTRY_ID = B.ENTRY_ID
11 INSERT INTO LOG_ENTRY_INC2(ENTRY_ID,ARJ_MARK,CREATE_TIME,USING_FLAG)
12 SELECT ENTRY_ID,'M',GETDATE(),'0000000000'
13 FROM INSERTED where ENTRY_ID NOT IN (SELECT ENTRY_ID FROM LOG_ENTRY_INC2)
14CREATE TRIGGER TRG_GUARANTEE_R_DEL_HG
15ON GUARANTEE_R
16FOR DELETE
17AS
18UPDATE A SET ARJ_MARK='D',CREATE_TIME=GETDATE(),USING_FLAG='0000000000'
19 FROM LOG_ENTRY_INC2 A INNER JOIN INSERTED B ON A.ENTRY_ID = B.ENTRY_ID
20INSERT INTO LOG_ENTRY_INC2(ENTRY_ID,ARJ_MARK,CREATE_TIME,USING_FLAG)
21 SELECT ENTRY_ID,'D',GETDATE(),'0000000000'
22 FROM DELETED where ENTRY_ID NOT IN (SELECT ENTRY_ID FROM LOG_ENTRY_INC2)
另外需要说明的:
触发器没有updated,更新时,旧数据是deleted,新数据是inserted
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。