触发器在增量同步数据的运用.

来源：互联网发布：网络架构师工作时间编辑：程序博客网时间：2024/04/28 01:06

触发器可以记录对表的添加,修改,删除,
这样可以通过触发器,记录一段时间内的表的变动的记录,把这些记录存到一个变动记录表里,
数据同步的时候,就可以读取这张变成的记录表,只需要同步变动过的记录,这样可以大大提高同步的速度.
对原表删除的操作,可以能新表做DELETE操作
对原表进行Insert 或Update操作,可以执行新建或更新操作.
下面是一个触发器(SQL SERVER 2000)的例子:

if exists (select * from dbo.sysobjects where id = object_id(N'[risk].[TRG_GUARANTEE_R_IN_HG]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
2

drop trigger [risk].[TRG_GUARANTEE_R_IN_HG]
3

if exists (select * from dbo.sysobjects where id = object_id(N'[risk].[TRG_GUARANTEE_R_DEL_HG]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
4

drop trigger [risk].[TRG_GUARANTEE_R_DEL_HG]
5

CREATE TRIGGER TRG_GUARANTEE_R_IN_HG
6

ON GUARANTEE_R
7

FOR INSERT,UPDATE
8

AS
9

UPDATE 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)
14

CREATE TRIGGER TRG_GUARANTEE_R_DEL_HG
15

ON GUARANTEE_R
16

FOR DELETE
17

AS
18

UPDATE 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
20

INSERT 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