表同步更新的問題的觸發(fā)器(SQLSERVER)

2024-07-21 02:44:41

字體：大中小

供稿：網(wǎng)友

sql server 2000 觸發(fā)器,表同步更新的問題
　　有三個(gè)表，A ，B，C
　　A、B表中含有: A1,B1,C1 三個(gè)字段,
　　C 表中存放A、B表中的A1、B1、C1 的集合,
　　字段類型都為nvarchar(10),
　　當(dāng)表A的數(shù)據(jù)被更新、刪除、插入后要反映到C表。
　　當(dāng)表B的數(shù)據(jù)被更新、刪除、插入后要反映到C表。
　　假定A,B表中在a1,b1,c1上有唯一索引

　　這個(gè)問題如果純屬?gòu)睦碚搧?lái)說(shuō)，是很容易解決的，因?yàn)閺囊罂芍?，?shí)質(zhì)上C表存放的數(shù)據(jù)即為A、B表的并集?？梢栽贏、B表上創(chuàng)建相同的trigger，一旦A、B表上有變化，比如插入、刪除或更新時(shí)，即清空C表數(shù)據(jù)，然后把A、B表的數(shù)據(jù)union后插入C表中即可實(shí)現(xiàn)目的:)呵呵呵。。。

　　下面的trigger的實(shí)現(xiàn)原理是：

　　當(dāng)A表插入數(shù)據(jù)時(shí)，檢查C表中是否有A表將要插入的數(shù)據(jù)，如果無(wú)，則將這行數(shù)據(jù)插入到C表中，反之，則不需要操作。

　　當(dāng)A表update時(shí), 檢查B表中是否有更新前這行數(shù)據(jù)，如果有，則C表中應(yīng)該保留這行數(shù)據(jù)且把A表中更新后的數(shù)據(jù)也插入到C表中去。如果B表中沒有A表更新前的這行數(shù)據(jù)且C表中沒有A表更新后的這行數(shù)據(jù)，則需要用A表更新后的數(shù)據(jù)來(lái)更新C表中與A表更新前這行數(shù)據(jù)相同的數(shù)據(jù)；如果B表中沒有A表更新的的這行數(shù)據(jù)且C表中有A表更新后的這行數(shù)據(jù)，則需要從C表中刪除跟A表更新前相同的那行數(shù)據(jù)（因?yàn)楦翧表后，A表和B表都沒有A表更新前的那行數(shù)據(jù)了，則這行數(shù)據(jù)顯然在C表中不應(yīng)該再存在了）。

　　當(dāng)A表中刪除時(shí)，檢查B表是否還存在A表要?jiǎng)h除的這行數(shù)據(jù)，如果有，則不能刪除C表中與A表要?jiǎng)h除的數(shù)據(jù)相同的行。反之，則執(zhí)行刪除操作。

　　B表中的trigger跟A表中的原理相同。

　　CREATE TRIGGER SYNC_C_BY_A
　　ON A
　　AFTER INSERT,UPDATE,DELETE
　　AS
　　Declare @DmlTinyInt --1:Insert 2:Update 3:Delete
　　Declare @RowsD    Int
　　Declare @RowsI    Int
　　Declare @A1_D     nvarchar(10)
　　Declare @B1_D     Nvarchar(10)
　　Declare @C1_D     Nvarchar(10)
　　--確定是哪一種dml操作
　　Select @RowsD=Count(*) From Deleted
　　Select @RowsI=Count(*) From Inserted
　　If @RowsD=0 And @RowsI=0
　　    Goto Exit_
　　If @RowsD=0 And @RowsI>0
　　    Set @Dml=1
　　Else
　　   If @RowsD>0 And @RowsI>0
　　 Set @Dml=2
　　   Else
　　 If @RowsD>0 And @RowsI=0
　　     Set @Dml=3
　　IF @DML=1
　　   BEGIN
　　 --檢查c表中是否已經(jīng)有A表中新插入的數(shù)據(jù)行,如果沒有,則也插入
　　 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)
　　    insert into c select * from inserted
　　   END
　　IF @DML=2
　　   BEGIN
　　 --檢查B表中是否有A表中更新前的這行數(shù)據(jù),如果有,則不需要更新C表中的數(shù)據(jù),而是要把A表中更新后的這行數(shù)據(jù)插入到C表中
　　 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)
　　   BEGIN
　　 --如果C表中不存在A表更新后的這行數(shù)據(jù),則更新C表中跟A表更新前那行數(shù)據(jù)相同的數(shù)據(jù)
　　 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)
　　     BEGIN
　　   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
　　     END
　　 --如果C表中存在A表更新后的這行數(shù)據(jù),則需要?jiǎng)h除C表中跟A表更新前相同的那行數(shù)據(jù)
　　 ELSE
　　     BEGIN
　　   SELECT @A1_D=A1,@B1_D=B1,@C1_D=C1 FROM DELETED
　　   DELETE FROM C WHERE @A1_D=A1 AND @B1_D=B1 AND @C1_D=C1
　　     END
　　   END
　　 ELSE
　　    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)
　　   END
　　IF @DML=3
　　   BEGIN
　　 --如果B表中不存在A表要?jiǎng)h除的這行數(shù)據(jù),則需要從C表中刪除這行數(shù)據(jù)
　　 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)
　　 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)
　　   END
　　EXIT_:

　　CREATE TRIGGER SYNC_C_BY_B
　　ON B
　　AFTER INSERT,UPDATE,DELETE
　　AS
　　Declare @DmlTinyInt --1:Insert 2:Update 3:Delete
　　Declare @RowsD    Int
　　Declare @RowsI    Int
　　Declare @A1_D     nvarchar(10)
　　Declare @B1_D     Nvarchar(10)
　　Declare @C1_D     Nvarchar(10)
　　--確定是哪一種dml操作
　　Select @RowsD=Count(*) From Deleted
　　Select @RowsI=Count(*) From Inserted
　　If @RowsD=0 And @RowsI=0
　　    Goto Exit_
　　If @RowsD=0 And @RowsI>0
　　    Set @Dml=1
　　Else
　　   If @RowsD>0 And @RowsI>0
　　 Set @Dml=2
　　   Else
　　 If @RowsD>0 And @RowsI=0
　　     Set @Dml=3
　　IF @DML=1
　　   BEGIN
　　 --檢查c表中是否已經(jīng)有B表中新插入的數(shù)據(jù)行,如果沒有,則也插入
　　 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)
　　    insert into c select * from inserted
　　   END
　　IF @DML=2
　　   BEGIN
　　 --檢查B表中是否有A表中更新前的這行數(shù)據(jù),如果有,則不需要更新C表中的數(shù)據(jù),而是要把A表中更新后的這行數(shù)據(jù)插入到C表中
　　 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)
　　   BEGIN
　　 --如果C表中不存在B表更新后的這行數(shù)據(jù),則更新C表中跟b表更新前那行數(shù)據(jù)相同的數(shù)據(jù)
　　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)
　　    BEGIN
　　 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
　　    END
　　 --如果C表中存在更新B表后的這行數(shù)據(jù),則需要?jiǎng)h除C表中跟B表更新前相同的那行數(shù)據(jù)
　　ELSE
　　    BEGIN
　　 SELECT @A1_D=A1,@B1_D=B1,@C1_D=C1 FROM DELETED
　　 DELETE FROM C WHERE @A1_D=A1 AND @B1_D=B1 AND @C1_D=C1
　　    End
　　
　　   END
　　 ELSE
　　    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)
　　   END
　　IF @DML=3
　　   BEGIN
　　 --如果A表中不存在B表要?jiǎng)h除的這行數(shù)據(jù),則需要從C表中刪除這行數(shù)據(jù)
　　 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)
　　 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)
　　   END
　　EXIT_: