触发器在增量同步数据的运用. (摘)
触发器可以记录对表的添加,修改,删除,
这样可以通过触发器,记录一段时间内的表的变动的记录,把这些记录存到一个变动记录表里,
数据同步的时候,就可以读取这张变成的记录表,只需要同步变动过的记录,这样可以大大提高同步的速度.
对原表删除的操作,可以能新表做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)
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)
![](https://edobnet.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://edobnet.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://edobnet.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://edobnet.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://edobnet.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://edobnet.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://edobnet.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://edobnet.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://edobnet.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://edobnet.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://edobnet.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://edobnet.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://edobnet.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://edobnet.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://edobnet.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://edobnet.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://edobnet.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://edobnet.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://edobnet.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://edobnet.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://edobnet.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://edobnet.cnblogs.com/Images/OutliningIndicators/None.gif)
另外需要说明的:
触发器没有updated,更新时,旧数据是deleted,新数据是inserted
posted on 2006-02-26 14:57 littlewood 阅读(340) 评论(0) 编辑 收藏 举报