表同步更新的问题的触发器
- 中国WEB开发者网络 (http://www.webasp.net)
-- 技术教程 (http://www.webasp.net/article/)
--- 表同步更新的问题的触发器 (http://www.webasp.net/article/25/24554.htm)
-- 发布日期： 2005-05-30
1
sql server 2000 触发器,表同步更新的问题
2有三个表，A ，B，C
3A、B表中含有: A1,B1,C1 三个字段,
4C 表中存放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
25CREATE TRIGGER SYNC_C_BY_A
26ON A
27AFTER INSERT,UPDATE,DELETE
28AS
29Declare @Dml TinyInt --1:Insert 2:Update 3:Delete
30Declare @RowsD Int
31Declare @RowsI Int
32Declare @A1_D nvarchar(10)
33Declare @B1_D Nvarchar(10)
34Declare @C1_D Nvarchar(10)
35--确定是哪一种dml操作
36Select @RowsD=Count(*) From Deleted
37Select @RowsI=Count(*) From Inserted
38If @RowsD=0 And @RowsI=0
39 Goto Exit_
40If @RowsD=0 And @RowsI>0
41 Set @Dml=1
42Else
43 If @RowsD>0 And @RowsI>0
44 Set @Dml=2
45 Else
46 If @RowsD>0 And @RowsI=0
47 Set @Dml=3
48IF @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
54IF @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
74IF @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
80EXIT_:
81
82CREATE TRIGGER SYNC_C_BY_B
83ON B
84AFTER INSERT,UPDATE,DELETE
85AS
86Declare @Dml TinyInt --1:Insert 2:Update 3:Delete
87Declare @RowsD Int
88Declare @RowsI Int
89Declare @A1_D nvarchar(10)
90Declare @B1_D Nvarchar(10)
91Declare @C1_D Nvarchar(10)
92--确定是哪一种dml操作
93Select @RowsD=Count(*) From Deleted
94Select @RowsI=Count(*) From Inserted
95If @RowsD=0 And @RowsI=0
96 Goto Exit_
97If @RowsD=0 And @RowsI>0
98 Set @Dml=1
99Else
100 If @RowsD>0 And @RowsI>0
101 Set @Dml=2
102 Else
103 If @RowsD>0 And @RowsI=0
104 Set @Dml=3
105IF @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
111IF @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
132IF @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
138EXIT_:
sql server 2000 触发器,表同步更新的问题 2
有三个表，A ，B，C3
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_A26
ON A27
AFTER INSERT,UPDATE,DELETE28
AS29
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=149
BEGIN50
--检查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 inserted53
END54
IF @DML=255
BEGIN56
--检查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
BEGIN62
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.C163
END64
--如果C表中存在A表更新后的这行数据,则需要删除C表中跟A表更新前相同的那行数据65
ELSE66
BEGIN67
SELECT @A1_D=A1,@B1_D=B1,@C1_D=C1 FROM DELETED68
DELETE FROM C WHERE @A1_D=A1 AND @B1_D=B1 AND @C1_D=C169
END70
END71
ELSE72
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
END74
IF @DML=375
BEGIN76
--如果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
END80
EXIT_: 81
82
CREATE TRIGGER SYNC_C_BY_B 83
ON B84
AFTER INSERT,UPDATE,DELETE85
AS86
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=1106
BEGIN107
--检查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 inserted110
END111
IF @DML=2112
BEGIN113
--检查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
BEGIN119
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.C1120
END121
--如果C表中存在更新B表后的这行数据,则需要删除C表中跟B表更新前相同的那行数据122
ELSE123
BEGIN124
SELECT @A1_D=A1,@B1_D=B1,@C1_D=C1 FROM DELETED125
DELETE FROM C WHERE @A1_D=A1 AND @B1_D=B1 AND @C1_D=C1126
End127
128
END129
ELSE130
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
END132
IF @DML=3133
BEGIN134
--如果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
END138
EXIT_: