1
sql server
2000
触发器,表同步更新的问题
2
有三个表，A ，B，C
3
A、B表中含有: A1,B1,C1 三个字段,
4
C 表中存放A、B表中的A1、B1、C1 的集合,
5
字段类型都为nvarchar(
10
),
6
当表A的数据被更新、删除、插入后要反映到C表。
7
当表B的数据被更新、删除、插入后要反映到C表。
8
假定A,B表中在a1,b1,c1上有唯一索引
9
10
11
这个问题如果纯属从理论来说，是很容易解决的，因为从要求可知，实质上C表存放的数据即为A、B表的并集。可以在A、B表上创建相同的trigger，一旦A、B表上有变化，比如插入、删除或更新时，即清空C表数据，然后把A、B表的数据union后插入C表中即可实现目的:)呵呵呵。。。
12
13
下面的trigger的实现原理是：
14
15
当A表插入数据时，检查C表中是否有A表将要插入的数据，如果无，则将这行数据插入到C表中，反之，则不需要操作。
16
17
当A表update时, 检查B表中是否有更新前这行数据，如果有，则C表中应该保留这行数据且把A表中更新后的数据也插入到C表中去。如果B表中没有A表更新前的这行数据且C表中没有A表更新后的这行数据，则需要用A表更新后的数据来更新C表中与A表更新前这行数据相同的数据；如果B表中没有A表更新的的这行数据且C表中有A表更新后的这行数据，则需要从C表中删除跟A表更新前相同的那行数据（因为更新A表后，A表和B表都没有A表更新前的那行数据了，则这行数据显然在C表中不应该再存在了）。
18
19
当A表中删除时，检查B表是否还存在A表要删除的这行数据，如果有，则不能删除C表中与A表要删除的数据相同的行。反之，则执行删除操作。
20
21
22
B表中的trigger跟A表中的原理相同。
23
24
25
CREATE
TRIGGER
SYNC_C_BY_A
26
ON
A
27
AFTER
INSERT
,
UPDATE
,
DELETE
28
AS
29
Declare
@Dml
TinyInt
--
1:Insert 2:Update 3:Delete
30
Declare
@RowsD
Int
31
Declare
@RowsI
Int
32
Declare
@A1_D
nvarchar
(
10
)
33
Declare
@B1_D
Nvarchar
(
10
)
34
Declare
@C1_D
Nvarchar
(
10
)
35
--
确定是哪一种dml操作
36
Select
@RowsD
=
Count
(
*
)
From
Deleted
37
Select
@RowsI
=
Count
(
*
)
From
Inserted
38
If
@RowsD
=
0
And
@RowsI
=
0
39
Goto
Exit_
40
If
@RowsD
=
0
And
@RowsI
>
0
41
Set
@Dml
=
1
42
Else
43
If
@RowsD
>
0
And
@RowsI
>
0
44
Set
@Dml
=
2
45
Else
46
If
@RowsD
>
0
And
@RowsI
=
0
47
Set
@Dml
=
3
48
IF
@DML
=
1
49
BEGIN
50
--
检查c表中是否已经有A表中新插入的数据行,如果没有,则也插入
51
IF
NOT
EXISTS
(
SELECT
TOP
1
1
FROM
c,inserted i
where
c.a1
=
i.a1
and
c.b1
=
i.b1
and
c.c1
=
i.c1)
52
insert
into
c
select
*
from
inserted
53
END
54
IF
@DML
=
2
55
BEGIN
56
--
检查B表中是否有A表中更新前的这行数据,如果有,则不需要更新C表中的数据,而是要把A表中更新后的这行数据插入到C表中
57
IF
NOT
EXISTS
(
SELECT
TOP
1
1
FROM
B,DELETED d
where
b.a1
=
d.a1
and
b.b1
=
d.b1
and
b.c1
=
d.c1)
58
BEGIN
59
--
如果C表中不存在A表更新后的这行数据,则更新C表中跟A表更新前那行数据相同的数据
60
IF
NOT
EXISTS
(
SELECT
TOP
1
1
FROM
C,INSERTED I
WHERE
C.A1
=
I.A1
AND
C.B1
=
I.B1
AND
C.C1
=
I.C1)
61
BEGIN
62
UPDATE
C
SET
A1
=
I.A1,B1
=
I.B1,C1
=
I.C1
FROM
C,INSERTED I,DELETED D
WHERE
C.A1
=
D.A1
AND
C.B1
=
D.B1
AND
C.C1
=
D.C1
63
END
64
--
如果C表中存在A表更新后的这行数据,则需要删除C表中跟A表更新前相同的那行数据
65
ELSE
66
BEGIN
67
SELECT
@A1_D
=
A1,
@B1_D
=
B1,
@C1_D
=
C1
FROM
DELETED
68
DELETE
FROM
C
WHERE
@A1_D
=
A1
AND
@B1_D
=
B1
AND
@C1_D
=
C1
69
END
70
END
71
ELSE
72
insert
into
c
select
*
from
inserted i
where
not
exists
(
select
1
from
c
where
i.a1
=
c.a1
and
i.b1
=
c.b1
and
i.c1
=
c.c1)
73
END
74
IF
@DML
=
3
75
BEGIN
76
--
如果B表中不存在A表要删除的这行数据,则需要从C表中删除这行数据
77
IF
not
exists
(
select
top
1
1
from
b,deleted d
where
b.a1
=
d.a1
and
b.b1
=
d.b1
and
b.c1
=
d.c1)
78
DELETE
FROM
C
WHERE
EXISTS
(
SELECT
1
FROM
deleted d
where
c.a1
=
d.a1
and
c.b1
=
d.b1
and
c.c1
=
d.c1)
79
END
80
EXIT_:
81
82
CREATE
TRIGGER
SYNC_C_BY_B
83
ON
B
84
AFTER
INSERT
,
UPDATE
,
DELETE
85
AS
86
Declare
@Dml
TinyInt
--
1:Insert 2:Update 3:Delete
87
Declare
@RowsD
Int
88
Declare
@RowsI
Int
89
Declare
@A1_D
nvarchar
(
10
)
90
Declare
@B1_D
Nvarchar
(
10
)
91
Declare
@C1_D
Nvarchar
(
10
)
92
--
确定是哪一种dml操作
93
Select
@RowsD
=
Count
(
*
)
From
Deleted
94
Select
@RowsI
=
Count
(
*
)
From
Inserted
95
If
@RowsD
=
0
And
@RowsI
=
0
96
Goto
Exit_
97
If
@RowsD
=
0
And
@RowsI
>
0
98
Set
@Dml
=
1
99
Else
100
If
@RowsD
>
0
And
@RowsI
>
0
101
Set
@Dml
=
2
102
Else
103
If
@RowsD
>
0
And
@RowsI
=
0
104
Set
@Dml
=
3
105
IF
@DML
=
1
106
BEGIN
107
--
检查c表中是否已经有B表中新插入的数据行,如果没有,则也插入
108
IF
NOT
EXISTS
(
SELECT
TOP
1
1
FROM
c,inserted i
where
c.a1
=
i.a1
and
c.b1
=
i.b1
and
c.c1
=
i.c1)
109
insert
into
c
select
*
from
inserted
110
END
111
IF
@DML
=
2
112
BEGIN
113
--
检查B表中是否有A表中更新前的这行数据,如果有,则不需要更新C表中的数据,而是要把A表中更新后的这行数据插入到C表中
114
IF
NOT
EXISTS
(
SELECT
TOP
1
1
FROM
A,DELETED d
where
a.a1
=
d.a1
and
a.b1
=
d.b1
and
a.c1
=
d.c1)
115
BEGIN
116
--
如果C表中不存在B表更新后的这行数据,则更新C表中跟b表更新前那行数据相同的数据
117
IF
NOT
EXISTS
(
SELECT
TOP
1
1
FROM
C,INSERTED I
WHERE
C.A1
=
I.A1
AND
C.B1
=
I.B1
AND
C.C1
=
I.C1)
118
BEGIN
119
UPDATE
C
SET
A1
=
I.A1,B1
=
I.B1,C1
=
I.C1
FROM
C,INSERTED I,DELETED D
WHERE
C.A1
=
D.A1
AND
C.B1
=
D.B1
AND
C.C1
=
D.C1
120
END
121
--
如果C表中存在更新B表后的这行数据,则需要删除C表中跟B表更新前相同的那行数据
122
ELSE
123
BEGIN
124
SELECT
@A1_D
=
A1,
@B1_D
=
B1,
@C1_D
=
C1
FROM
DELETED
125
DELETE
FROM
C
WHERE
@A1_D
=
A1
AND
@B1_D
=
B1
AND
@C1_D
=
C1
126
End
127
128
END
129
ELSE
130
insert
into
c
select
*
from
inserted i
where
not
exists
(
select
1
from
c
where
i.a1
=
c.a1
and
i.b1
=
c.b1
and
i.c1
=
c.c1)
131
END
132
IF
@DML
=
3
133
BEGIN
134
--
如果A表中不存在B表要删除的这行数据,则需要从C表中删除这行数据
135
if
not
exists
(
select
top
1
1
from
a,deleted d
where
a.a1
=
d.a1
and
a.b1
=
d.b1
and
a.c1
=
d.c1)
136
DELETE
FROM
C
WHERE
EXISTS
(
SELECT
1
FROM
deleted d
where
c.a1
=
d.a1
and
c.b1
=
d.b1
and
c.c1
=
d.c1)
137
END
138
EXIT_: