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
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.