0

I have two tables:

Products:

Product ID | Quantity

OrdersLines:

Product ID | Amount --(multiple lines with the same ID)

Naturally, before I delete the product from the Products table, I need to delete all order lines with that product id first from the OrdersLines table.

So I wrote a trigger to do it:

CREATE TRIGGER [dbo].[atbl_Sales_Products_DTrig]
ON [dbo].[atbl_Sales_Products]
FOR DELETE
AS
BEGIN
    DELETE FROM atbl_sales_OrdersLines
    WHERE ProductID = (SELECT ProductID FROM deleted)
END

However, when I try do delete in the form from Products table, it still says:

There are rows in a related table (Sales OrdersLines) that must be removed before this row can be deleted

What am I doing wrong here?

EDIT as per request: Main constraint: 

[dbo].[atbl_Sales_OrdersLines]  WITH CHECK ADD  CONSTRAINT 
[FK_atbl_Sales_OrdersLines_atbl_Sales_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[atbl_Sales_Products] ([ProductID])
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Benua
  • 269
  • 1
  • 3
  • 19
  • 2
    Is there a reason you don't want to use a [cascading delete](https://stackoverflow.com/a/6260736/300836)? – Matt Gibson Jul 26 '17 at 09:53
  • Yes, i know that this would fix the problem. However, im doing this for assignment. It needs to be solved in a trigger. – Benua Jul 26 '17 at 09:55
  • 1
    Trigger (it is called `AFTER` trigger) is executed after the delete operation in the table, hence the error message. What you need is an `INSTEAD OF` trigger, they are a bit trickier to get right. Read the whole article: https://learn.microsoft.com/en-us/sql/relational-databases/triggers/dml-triggers – Alex Jul 26 '17 at 10:00

1 Answers1

2

The main problem in this case is that trigger was created as FOR DELETE trigger. So it's fired after delete operation - which cause error.

You should create it as INSTEAD OF DELETE trigger and it will solve this problem.

Second problem is that you shouldn't use = in subquery bacause it can return more then one row. You should use IN operator.

You can see example below which cause error for both problems.

INSERT INTO Products(ProductId, Quantity)
SELECT 1,1
UNION
SELECT 2,2
GO

INSERT INTO OrdersLines(ProductId,Amount)
SELECT 1,2
UNION
SELECT 1,3
UNION
SELECT 2,4
UNION
SELECT 2,5
GO

DELETE Products
GO

This will pass when the trigger is fine.

Paweł Tajs
  • 407
  • 4
  • 12
  • Thanks, this solved my problem. Just a little comment, i needed to write an additional query to delete from the products table as well. – Benua Jul 26 '17 at 10:35