sql-server - 在 SQL 触发器中使用求和操作进行多行更新

Question

我有 3 张桌子。我想通过更新将表中的数据总和添加到另一个表中。

   CREATE TRIGGER [active_plan_increase_total]
   ON [dbo].[wallet_plan]
   after UPDATE
   AS
   BEGIN
       SET NOCOUNT ON
       declare @id int, @user_id  int, @inc_id int, @inc_rate float, @status nvarchar(50), @current_balance float
       
       select @user_id = inserted.user_id, @inc_id = inserted.rate_id, @status= inserted.status from inserted
       
       select @inc_rate = sum( Increase_Rate.inc_rate ), @user_id =wallet_plan.user_id from Increase_Rate join inserted i on  Increase_Rate.Id = i.rate_id inner join wallet_plan on wallet_plan.user_id = i.user_id where wallet_plan.user_id = i.user_id group by wallet_plan.user_id 

       select @current_balance = User_Total.current_btc from User_Total where User_Total.user_id = @user_id    
       
       update User_Total set User_Total.current_btc = (User_Total.current_btc + Increase_Rate.inc_rate)  from User_Total join inserted i on User_Total.user_id = i.user_id 
       inner join Increase_Rate on Increase_Rate.Id = i.rate_id 

   END

这段代码工作正常，但我想使用 sum 来增加速率。因为有时一个 id 有多个 inc_rates。如何将这两个查询组合为多行。

我有一个名为“增加率”的表。这个增加率 id 与 rate_id 相关，我想得到与 user_id 相关的 inc_rate 的总和。我想将这些结果添加到 user_total 的当前余额数据中。增长率多行，用户总数多行。如何编写连接该表的触发器。

    CREATE TRIGGER [active_plan_increase_total]
    ON [dbo].[wallet_plan]
    after UPDATE
    AS
    BEGIN
        SET NOCOUNT ON
        declare @id int, @user_id  int, @inc_id int, @inc_rate float, @status nvarchar(50), @current_balance float
        
        select @user_id = inserted.user_id, @inc_id = inserted.rate_id, @status= inserted.status from inserted      
        
        select @inc_rate = sum( Increase_Rate.inc_rate ), @user_id =wallet_plan.user_id from Increase_Rate join inserted i on  Increase_Rate.Id = i.rate_id inner join wallet_plan on wallet_plan.user_id = i.user_id where wallet_plan.user_id = i.user_id group by wallet_plan.user_id 

        select @current_balance = User_Total.current_btc from User_Total where User_Total.user_id = @user_id    
                
        update User_Total set User_Total.current_btc =( User_Total.current_btc +  @inc_rate)   from  User_Total  join inserted i on User_Total.user_id = i.user_id  
        inner join Increase_Rate on Increase_Rate.Id = i.rate_id where User_Total.user_id = i.user_id

    END

这是向所有用户添加最后一个 inc_rate。这必须为每个用户添加不同的增长率总和。

标签： sql-servertriggers

Answer 1

根据 DaleK，触发器应该允许多行插入，或者它可以引发错误或抛出。此外，无论“插入”伪表中是否有任何行，触发器都会执行，因此最好确保在没有插入任何内容时不执行任何操作（如果合适的话）。我的重构想出了这个

CREATE TRIGGER [active_plan_increase_total]
ON [dbo].[wallet_plan]
after UPDATE
AS
SET NOCOUNT ON;
BEGIN
if exists (select * from inserted)
        with rate_incr_cte([user_id], incr_rate) as (
            select i.[user_id], sum(ir.inc_rate)
            from Increase_Rate ir 
                 join inserted i on  ir.Id = i.rate_id 
            group by i.[user_id]) 
        update ut 
        set current_btc =( User_Total.current_btc+ic.incr_rate)
        from User_Total ut
             join rate_incr_cte ic on ut.[user_id] = ic.[user_id];
END