CODEWITHSUNDEEP
sql-servert-sql
Benua
Benua

Reputation: 269

SQL trigger to delete from another table

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])

Upvotes: 0

Views: 4131

Answers (1)

Paweł Tajs
Paweł Tajs

Reputation: 482

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.

Upvotes: 2

Related Questions