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_:
免责声明：本文为网络用户发布，其观点仅代表作者个人观点，与本站无关，本站仅提供信息存储服务。文中陈述内容未经本站证实，其真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
