触发器实现表数据自动更新

最近做的项目需要实现基础数据表有数据插入的时候统计数据表能自动更新，要实现此需求目前有两种方案。方案一是通过Job定时执行，计算基础数据表中的数据，然后更新统计表；方案二采用触发器，因为已知基础数据表只会有数据插入操作，不会更新，可以建立插入触发器。比较两种方案，考虑到系统访问的实时性比较高，因此决定采用方案二。

基础表 [dbo].[table1] 的建表语句

[sql] 
CREATE TABLE [dbo].[table1](  
    [id] [int] NOT NULL,  
    [amount] [int] NOT NULL,  
    [type] [varchar](50) NULL  
) ON [PRIMARY]

统计表 [dbo].[table5] 的建表语句

[sql] 
CREATE TABLE [dbo].[table5](  
    [id] [int] NOT NULL,  
    [sum_amount] [int] NOT NULL,  
    [avg_amount] [int] NOT NULL  
) ON [PRIMARY]  
  
GO

每当 table1 插入数据的时候，就需要更新 table5 ，根据 table1 中的 id 统计不同 id 的总的数量，以及不同 id 按 type 求总的数量的平均值。可以在 table1 上建立如下触发器实现。

[sql] 
-- =============================================  
-- Author:      <Author,,Name>  
-- Create date: <Create Date,,>  
-- Description: <Description,,>  
-- =============================================  
CREATE TRIGGER [dbo].[tr_statamount]  
   ON  [dbo].[table1]  
   AFTER insert  
AS   
BEGIN  
    update table5 set sum_amount=a.sum_amount,avg_amount=a.avg_amount  
    from  
    (  
        select t.id,sum(amount) as sum_amount,avg(amount) as avg_amount from  
        (  
            select inserted.* from inserted   
            inner join table5 on inserted.id=table5.id  
            union   
            select table1.* from table1   
            inner join table5 on table1.id=table5.id  
        )t group by t.id  
    ) a inner join table5 b on b.id=a.id  
      
  
    insert into table5   
    select t.id,t.amount,t.amount from inserted t  
    left join table5 a on t.id=a.id  
    where a.id is null  
END  
  
GO

例如当前 table1 和 table5 的数据如下

现在插入一条全新 id 的数据，该 id 在 table1 和 table5 中均没有记录，那么这条记录应该是在插入 table1 的时候同时插入 table5。用如下插入数据脚本

[sql] 
INSERT INTO [master].[dbo].[table1]  
           ([id]  
           ,[amount]  
           ,[type])  
     VALUES  
           (5  
           ,200  
           ,'A')

查询基础数据表和统计表

再插入一条数据，该数据的 id 已经在 table1 中存在，那么 table1 增加一条数据，但是 table5 数据不增加，只是更新总量和平均量，例如执行下面的脚本

[sql] 
INSERT INTO [master].[dbo].[table1]  
           ([id]  
           ,[amount]  
           ,[type])  
     VALUES  
           (4  
           ,700  
           ,'A')

执行后，结果为

总结

通过触发器实现表数据的自动更新，关键是需要考虑全面，A表插入数据，B表自动更新，必须考虑B表自动更新的多种情况，可能A表的数据在B表不存在，可能A表的数据已经更新过B表，当然这只是一个简单的例子，实际情况可能会根据业务变得更加复杂，总之我们需要理清思路，通过流程图覆盖所有情形方能立于不败之地。