同步类触发器

 

触发器综述之三

 

同步类触发器

   

同步类触发器的功能是根据对表的操作，去同步更改另一个表，这类触发器是应用最多的，也是经常出问题的。《保持两表数据一致的触发器事例》　是之前贴的一个例子，说明了这类触发器需要注意的事项。

从功能上来说，一般此类触发器所在的表往往是一个经常被客户端程序操作的表，而触发器需要同步的表则不一定，有像《保持两表数据一致的触发器事例》需要双向同步的，也有很多单向同步的例子，比如用触发器把一个系统的输入数据自动倒入到另一个系统，特别是输入数据的系统是个“旧系统”，有可能没有源码，也没有足够的文档的情况，要想避免多次输入，触发器是不多的选择之一。另一方面，《保持两表数据一致的触发器事例》并没有对数据作变换，而实际应用中，往往需要对数据作各种各样的变换。

从技术上来说，这类触发器是比较复杂的，需要处理INSERT，UPDATE，DELETE操作，也需要注意可能一次操作多条记录，而且可能会有一些数据变换的需求。另外，在更新新表的时候，必须考虑已有记录和尚未有记录的两种情况都是可能的。虽然在有些地方已经提到，我在这里还是想强调下编写此类触发器需要注意的情况：

1、涉及的表最好有主键，没有主键也需要有相关的唯一约束。更新数据按照主键或者唯一索引更改。

2、必须考虑可能一次操作多条记录的情况，所谓可能一次操作多条记录的情况是，客户端提交的语句是可能影响多行的，比如

         INSERT     TAB(A,B) SELECT A,B FROM TAB1

    UPDATE  TAB SET B=1 WHERE A>2

    DELETE TAB WHERE C IS NULL

    等，如果影像多行，那么在触发器中，INSERTED和DELETED这两个虚表就不是只有一条记录，这样如下写法的触发器往往是有问题的：

　　DECLARE @A VARCHAR(20)

    SELECT @A=A FROM INSERTED

    IF @A=1

    　　UPDATE TAB1 SET B=1 WHERE ...

　　ELSE

        UPDATE TAB1 SET C=1 WHERE ...

 

    这种情况，最好的方法是分析需求，用表连接的方法来处理，如下：

         UPDATE TAB1 SET

                B=CASE WHEN I.A=1 THEN 1 ELSE TAB1.B END,

                   C= CASE WHEN I.A=1 THEN TAB1.C ELSE 1 END

    FROM INSERTED I,TAB1

    WHERE ...

         实在没有办法用连接写的就只能用游标处理了。

3、尽量不要在触发器中使用游标，如上说了，不能用连接写的就只能用游标的，但是我建议遇到必须用游标的时候应该再考虑几个问题：

         Ａ、是不是可以不用触发器。

         Ｂ、是不是能够限制一次只能操作一条记录。这个限制用触发器增加是非常简单的。

　　Ｃ、如果上面两条没法解决，那必须考虑这个触发器的表的数据量是否能够限制，比如用一个历史表和一个当前表的方法。

 

 

下面用一个简化了的例子来说明这类触发器。需求如下：

         一个业务系统，有一个业务表，记录业务数据，另一个记账系统，有一个金额表，需要记录这些业务的数据，但是不能重新输入，而且对实时性要求比较高，表结构也不相同，具体结构如下（无关字段已经忽略）：

 

--建立业务表  

CREATE   TABLE   TB(  

ID    INT   IDENTITY(1,1) PRIMARY KEY ,                                     --主键

DT         DATETIME   NOT   NULL   DEFAULT   (GETDATE()),                  --业务日期时间  

CODE        VARCHAR(20) NOT NULL ,                                                      --物品代码

TYPE     INT   NOT NULL DEFAULT(1),                                    --业务类型，我们可以简化为1--进　2--出  

PRICE   NUMERIC(10,2) NOT NULL DEFAULT(0),                                         --单价        

QTY       INT NOT NULL DEFAULT(0)                                        --数量

)  

GO  

   

--建立金额表  

CREATE   TABLE   TB1(  

ID    INT   IDENTITY(1,1) PRIMARY KEY ,                                     --主键

TID   INT   NOT NULL,                                                      --TB表ID

YEARS   INT  NOT NULL,                                                         --年份 

MONTHS  INT         NOT NULL,                                               --月份 

INMONEY NUMERIC(10,2)  NOT NULL DEFAULT(0),                          --进金额        

OUTMONEY NUMERIC(10,2) NOT NULL DEFAULT(0),                             --出金额

REM      VARCHAR(300)         NOT NULL DEFAULT('')                             --备注

)  

GO  

 

--触发器

CREATE TRIGGER TR_TB

ON TB

FOR          INSERT,DELETE,UPDATE

AS

 

SET           NOCOUNT        ON

--删除记录

DELETE    TB1

FROM      DELETED D

WHERE    D.ID=TB1.TID

 

--修改记录

UPDATE   TB1  SET

         YEARS       =YEAR(DT),

         MONTHS =MONTH(DT),

         INMONEY         =CASE WHEN TYPE=1 THEN PRICE*QTY ELSE 0 END,

         OUTMONEY=CASE WHEN TYPE=2 THEN PRICE*QTY ELSE 0 END,

         REM          =CASE WHEN TYPE=1 THEN '进' ELSE '出' END+CODE+'(数量:'+CAST(QTY AS VARCHAR)+',单价:'+CAST(PRICE AS VARCHAR)+')'

FROM      INSERTED I,TB1

WHERE   I.ID=TB1.TID

 

--增加记录

INSERT     TB1  (

         TID   ,

         YEARS       ,

         MONTHS ,

         INMONEY         ,

         OUTMONEY,

         REM          )

SELECT

         TID   =ID,

         YEARS       =YEAR(DT),

         MONTHS =MONTH(DT),

         INMONEY         =CASE WHEN TYPE=1 THEN PRICE*QTY ELSE 0 END,

         OUTMONEY=CASE WHEN TYPE=2 THEN PRICE*QTY ELSE 0 END,

         REM          =CASE WHEN TYPE=1 THEN '进' ELSE '出' END+CODE+'(数量:'+CAST(QTY AS VARCHAR)+',单价:'+CAST(PRICE AS VARCHAR)+')'

FROM       INSERTED I

WHERE    NOT EXISTS      (

         SELECT     1

         FROM       TB1

         WHERE    TID   =I.ID

         )

 

GO

 

--测试插入一行数据

INSERT     TB    (

         DT         ,

         CODE        ,

         TYPE     ,

         PRICE   ,

         QTY       )

VALUES              (

         GETDATE(),

         '0001'       ,

         1       ,

         102.10      ,

         2000         )

 

SELECT * FROM TB

SELECT * FROM TB1

GO

  
         --结果

 

 

 

--测试插入多行数据

INSERT     TB    (

         DT         ,

         CODE        ,

         TYPE     ,

         PRICE   ,

         QTY       )

SELECT

         GETDATE(),

         '0002'       ,

         1       ,

         12.20        ,

         100 

UNION    ALL

SELECT

         GETDATE(),

         '0001'       ,

         2       ,

         110  ,

         200 

 

 

SELECT * FROM TB

SELECT * FROM TB1

GO

 

 

 

 

 

 

--测试修改多行数据

UPDATE   TB    SET

         PRICE       =105

WHERE    CODE        ='0001'

 

SELECT * FROM TB

SELECT * FROM TB1

GO

--结果

 

 

 

 

 

注意这里TB1表的ID也改变了，这是因为在触发器处理中，先删除了数据后插入数据引起的。

 

--测试删除多行数据

DELETE    TB

WHERE    CODE        ='0001'

 

SELECT * FROM TB

SELECT * FROM TB1

GO

--结果

 

 

 

 

 

这是个数据作了简单变换的单向同步的同步类触发器例子，基本保证在TB输入的数据能同步反映到TB1，唯一不足的是处理方式，采取了先删除再插入的方式，不过可以修改触发器代码来修正，修正后触发起代码如下，有兴趣的可以自己测试下。

 

--修改后触发器

ALTER TRIGGER TR_TB

ON TB

FOR          INSERT,DELETE,UPDATE

AS

 

SET           NOCOUNT        ON

--删除记录

DELETE    TB1

FROM      DELETED D

WHERE    D.ID=TB1.TID

AND NOT EXISTS      (        --加这个判断不删除修改的记录的TB1对应记录

         SELECT     1

         FROM       INSERTED

         WHERE    ID=D.ID

         )

 

--修改记录

UPDATE   TB1  SET

         YEARS       =YEAR(DT),

         MONTHS =MONTH(DT),

         INMONEY         =CASE WHEN TYPE=1 THEN PRICE*QTY ELSE 0 END,

         OUTMONEY=CASE WHEN TYPE=2 THEN PRICE*QTY ELSE 0 END,

         REM          =CASE WHEN TYPE=1 THEN '进' ELSE '出' END+CODE+'(数量:'+CAST(QTY AS VARCHAR)+',单价:'+CAST(PRICE AS VARCHAR)+')'

FROM      INSERTED I,TB1

WHERE   I.ID=TB1.TID

 

--增加记录

INSERT     TB1  (

         TID   ,

         YEARS       ,

         MONTHS ,

         INMONEY         ,

         OUTMONEY,

         REM          )

SELECT

         TID   =ID,

         YEARS       =YEAR(DT),

         MONTHS =MONTH(DT),

         INMONEY         =CASE WHEN TYPE=1 THEN PRICE*QTY ELSE 0 END,

         OUTMONEY=CASE WHEN TYPE=2 THEN PRICE*QTY ELSE 0 END,

         REM          =CASE WHEN TYPE=1 THEN '进' ELSE '出' END+CODE+'(数量:'+CAST(QTY AS VARCHAR)+',单价:'+CAST(PRICE AS VARCHAR)+')'

FROM       INSERTED I

WHERE    NOT EXISTS      (

         SELECT     1

         FROM       TB1

         WHERE    TID   =I.ID

         )

 

GO

 

总结：同步类触发器在不同系统之间互通数据发挥一定的作用，可以使我们的用户在不必修改程序的前提下，减少重复输入数据，具体的应用有：

1、  所谓在一个旧系统进行二次开发的时候，可以用来从旧系统取得必要的数据。

2、  在企业信息化不是一步到位的情况下，后上系统需要用触发器从先上系统取得数据，减少输入。

同步类触发器编写的主要难点在于（说很多次了，再重复一次）：

1、对原表的所有操作（INSERT、DELETE、UPDATE）触发器中都必须考虑。

2、必须考虑批量操作的情况。不能用变量取出INSERTED、DELETED的字段值，必须用连接表来进行处理。

3、同时必须考虑性能问题。触发器对性能的影响是积累起来的，所以在写触发器的时候任何细节的对性能的影响都需要注意。