SQL trigger to delete from another table

Question

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

Yes, i know that this would fix the problem. However, im doing this for assignment. It needs to be solved in a trigger. — Benua
– Benua, Commented Jul 26, 2017 at 9:55
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: learn.microsoft.com/en-us/sql/relational-databases/triggers/… — Alex
– Alex, Commented Jul 26, 2017 at 10:00

Paweł Tajs · Accepted Answer · 2017-07-26 10:32:08Z

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.

edited Jul 26, 2017 at 10:32

answered Jul 26, 2017 at 10:27

Paweł Tajs

4824 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Benua Over a year ago

Thanks, this solved my problem. Just a little comment, i needed to write an additional query to delete from the products table as well.

Collectives™ on Stack Overflow

SQL trigger to delete from another table

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related