觸發器實現表資料自動更新

weixin_34198881發表於2019-01-08
觸發器

最近做的專案需要實現基礎資料表有資料插入的時候統計資料表能自動更新，要實現此需求目前有兩種方案。方案一是通過Job定時執行，計算基礎資料表中的資料，然後更新統計表；方案二採用觸發器，因為已知基礎資料表只會有資料插入操作，不會更新，可以建立插入觸發器。比較兩種方案，考慮到系統訪問的實時性比較高，因此決定採用方案二。

基礎表 [dbo].[table1]  的建表語句

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

統計表 [dbo].[table5] 的建表語句

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 上建立如下觸發器實現。

-- =============================================
-- 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。用如下插入資料指令碼

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


查詢基礎資料表和統計表

再插入一條資料，該資料的 id 已經在 table1 中存在，那麼 table1 增加一條資料，但是 table5 資料不增加，只是更新總量和平均量，例如執行下面的指令碼

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


執行後，結果為

總結

通過觸發器實現表資料的自動更新，關鍵是需要考慮全面，A表插入資料，B表自動更新，必須考慮B表自動更新的多種情況，可能A表的資料在B表不存在，可能A表的資料已經更新過B表，當然這只是一個簡單的例子，實際情況可能會根據業務變得更加複雜，總之我們需要理清思路，通過流程圖覆蓋所有情形方能立於不敗之地。

 

相關文章