表同步更新的问题的触发器

  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_: 

转载于:https://www.cnblogs.com/Dicky/archive/2005/05/21/160037.html