觸發多行動作

我已經撰寫了這個觸發器：

create or alter trigger tg_weight_sale_excedes_production on sales after insert, update
as
begin

    declare @productId int = (  select productId -- more than one row?
                                from inserted
                            )


    declare @producedWeight decimal(6,3) =  (
                                                select sum(weight)
                                                from product
                                                where productId = @productId
                                            )

    declare @soldWeight decimal(6,3) = (    select sum(sold_weight)
                                            from sales s
                                                inner join product p
                                            on s.productId = p.productId
                                            where s.productId = @productId
                                        ) 
    if (@soldWeight > @producedWeight)
    begin
        rollback tran
        raiserror('Sold weight excedes produced weight for this product', 16, 1)
    end
end
go

inserted但是，如果包含多于一行，這將不起作用，而且我真的不知道如何實作它。我也試過join insertedwith product，但它幾乎是一樣的。

uj5u.com熱心網友回復：

您可以使用基于集合的查詢來檢查其中的任何行inserted是否違反您的條件。您需要加入各種表，使用預聚合子查詢更容易。

請注意，您應該使用THROW而不是RAISERROR. 然后你不需要回滾，因為THROW會為你做

CREATE OR ALTER TRIGGER tg_weight_sale_excedes_production
ON sales AFTER INSERT, UPDATE
AS

IF EXISTS (SELECT 1
    FROM sales s
    WHERE s.productid IN (
        SELECT i.productid
        FROM inserted i
    )
    GROUP BY
      s.productid
    HAVING SUM(s.sold_weight) > (
        SELECT ISNULL(SUM(weight), 0)
        FROM product p
        WHERE p.productid = s.productid
    )
BEGIN
    THROW 50001, 'Sold weight exceedes produced weight for a product', 1;
END;

go

uj5u.com熱心網友回復：

您可以將現有邏輯包裝在游標回圈中，例如：

create or alter trigger tg_weight_sale_excedes_production on sales after insert, update
as
begin
  declare c cursor local for select productId from inserted
  declare @productId int 
  open c
  fetch next from c into @productId
  while @@FETCH_STATUS = 0
  begin

    declare @producedWeight decimal(6,3) =  (
                                                select sum(weight)
                                                from product
                                                where productId = @productId
                                            )

    declare @soldWeight decimal(6,3) = (    select sum(sold_weight)
                                            from sales s
                                                inner join product p
                                            on s.productId = p.productId
                                            where s.productId = @productId
                                        ) 
    if (@soldWeight > @producedWeight)
    begin
        close c
        deallocate c
        rollback tran
        raiserror('Sold weight excedes produced weight for this product', 16, 1)
    end
    fetch next from c into @productId
  end 

  close c
  deallocate c
end

或者將邏輯轉換為單個 EXISTS 查詢以檢查是否有任何行inserted違反規則。

標籤：sql服务器 tsql 触发器

上一篇：透視資料以顯示特定列的唯一值

下一篇：為另一個表中的每個ID插入一組行