Search code examples
sql-serverssms

How to delete rows one by one so that the trigger doesn't throw an error


I have a trigger that when a row is deleted in InvoiceDetails the row gets copied to InvoiceDetailsHistory. When I execute the sql "DELETE FROM InvoiceDetails " I get an error that says:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I supposed because I'm deleting the rows all at once. How could I fix this? Here's my trigger:

ALTER trigger [dbo].[deleteinvoicedetails]       
on [dbo].[InvoiceDetails]
AFTER DELETE 
AS
DECLARE @InvoiceID int,@FoodID int,@quantity int,@UnitPrice float,@totalprice float
BEGIN
SET @InvoiceID = (Select InvoiceID from deleted)
SET @FoodID = (Select FoodID from deleted)
SET @quantity = (Select quantity from deleted)
SET @UnitPrice = (Select UnitPrice from deleted)
SET @totalprice = (Select totalprice from deleted)
INSERT INTO InvoiceDetailsHistory VALUES (@InvoiceID,@FoodID,@quantity,@UnitPrice,@totalprice)
END

Solution

  • You're clearly still learning, so let me give an example of what @tomtom is referring to...

    ALTER trigger [dbo].[deleteinvoicedetails]       
    on [dbo].[InvoiceDetails]
    AFTER DELETE 
    AS
    BEGIN
    INSERT INTO
      InvoiceDetailsHistory
    SELECT
      InvoiceID,
      FoodID, 
      Quantity,
      UnitPrice,
      Totalprice
    FROM
      deleted
    END
    

    This works no matter how many rows are deleted.