触发器可以记录对表的添加,修改,删除,
这样可以通过触发器,记录一段时间内的表的变动的记录,把这些记录存到一个变动记录表里,
数据同步的时候,就可以读取这张变成的记录表,只需要同步变动过的记录,这样可以大大提高同步的速度.
对原表删除的操作,可以能新表做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)
![None.gif](/Images/OutliningIndicators/None.gif)
2
![None.gif](/Images/OutliningIndicators/None.gif)
3
![None.gif](/Images/OutliningIndicators/None.gif)
4
![None.gif](/Images/OutliningIndicators/None.gif)
5
![None.gif](/Images/OutliningIndicators/None.gif)
6
![None.gif](/Images/OutliningIndicators/None.gif)
7
![None.gif](/Images/OutliningIndicators/None.gif)
8
![None.gif](/Images/OutliningIndicators/None.gif)
9
![None.gif](/Images/OutliningIndicators/None.gif)
10
![None.gif](/Images/OutliningIndicators/None.gif)
11
![None.gif](/Images/OutliningIndicators/None.gif)
12
![None.gif](/Images/OutliningIndicators/None.gif)
13
![None.gif](/Images/OutliningIndicators/None.gif)
14
![None.gif](/Images/OutliningIndicators/None.gif)
15
![None.gif](/Images/OutliningIndicators/None.gif)
16
![None.gif](/Images/OutliningIndicators/None.gif)
17
![None.gif](/Images/OutliningIndicators/None.gif)
18
![None.gif](/Images/OutliningIndicators/None.gif)
19
![None.gif](/Images/OutliningIndicators/None.gif)
20
![None.gif](/Images/OutliningIndicators/None.gif)
21
![None.gif](/Images/OutliningIndicators/None.gif)
22
![None.gif](/Images/OutliningIndicators/None.gif)
另外需要说明的:
触发器没有updated,更新时,旧数据是deleted,新数据是inserted