I have coded this trigger:

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

However, this doesn't work if inserted contains more than one row, and I don't really know how to implement it. I've also tried to join inserted with product, but it's pretty much the same.

CodePudding user response：

You can use an set-based query to check for any rows within inserted that are against your condition. You need to join on the various tables, which is easier using pre-aggregated subqueries.

Note that you should use THROW rather than RAISERROR. You then don't need to rollback, as THROW will do that for you

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

CodePudding user response：

You can wrap your existing logic in a cursor loop, something like:

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

Or transform the logic to a single EXISTS query to check if any row in inserted violates the rule.