Using triggers to implement referential integrity actions (SQL Server)

Question

I'm trying to implement some triggers for my tables

2 of the tables are ORDERS (has orders) and ORDERS_ITEMS (has items for each order), and I want to delete the ORDER when there are no more ITEMS in that ORDER.

I want my trigger to look something like this

CREATE TRIGGER DELETE_ORDER_WHEN_NO_ITEMS
INSTEAD OF DELETE 
ON ORDER
    DECLARE rowcount int;
BEGIN
    // First detemine if this is the last item in the ORDER
    SELECT Count(*) 
    INTO rowcount 
    FROM ORDER_ITEM 
    WHERE ORDER_ITEM.ItemNumber = old:ItemNumber;

    // Delete ITEM row
    DELETE ORDER_ITEM 
    WHERE ORDER_ITEM.ItemNumber = old:ItemNumber;

    // Last ITEM in ORDER delete the whole ORDER 
    IF (rowcount = 1) THEN
       DELETE ORDER 
       WHERE ORDER.OrderNumber = old:OrderNumber;
    END IF
END;

I'm not sure how to write this in SQL Server and I had the algorithm from a book but I couldn't run it on SQL Server.

I'm just wondering why you are doing this on the DB? this sounds like a business rule, and would probably be easier and more manageable to handle in the code. — ZeRaTuL_jF
– ZeRaTuL_jF, Commented May 6, 2015 at 20:23
Please show the layout of your tables. It is not clear to me why a table called Order would have a column called ItemNumber. I would expect the foreign key relationship to be on a column such as OrderId. — Gordon Linoff
– Gordon Linoff, Commented May 6, 2015 at 20:32

Zohar Peled · Accepted Answer · 2015-05-07 05:42:33Z

2

Don't use a trigger for that. Use foriegn key with cascade delete.

Update

Sorry, I've jumped to the wrong conclution. If you want to delete the orders when there are no items connected to it, cascade delete will not help.
Use a triger for after delete on the order_item table and write this delete statement there: (updated again)

CREATE TRIGGER order_item_delete ON order_item
FOR DELETE
AS 

DELETE order
FROM order
INNER JOIN deleted ON (order.OrderNumber = deleted.OrderNumber) -- 1
LEFT JOIN order_Item ON(order.OrderNumber = order_item.OrderNumber) 
WHERE order_Item.ItemNumber IS NULL -- 2

GO

Breakdown:

The inner join with deleted table will ensures you only delete records where the OrderNumber is the same as the deleted records from order_item table.
The left join with order_item table along with the where clause ensures you only delete records from orders table if they have no order_item records attached to them.

edited May 7, 2015 at 5:42

answered May 6, 2015 at 20:12

Zohar Peled

82.9k11 gold badges80 silver badges134 bronze badges

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

9 Comments

Muhannad Over a year ago

but I thought that Cascade delete will work ONLY if you delete a parent then all children will be deleted too, but not the other way around?

ZeRaTuL_jF Over a year ago

@Zohar Peled From the question i get that he wants to delete Orders only when there is no more Order_Items that belong to that Order, not to Delete ALL Order_Items when he deletes the Order.

Muhannad Over a year ago

I really don't know where to put your code ? do I need IF condition? I mean in the Trigger itself?

Zohar Peled Over a year ago

See my edited answer. You don't need an if there. It might be a little slow uf you have many order items but i think for under 20,000-50,000 records you should be fine.

Muhannad Over a year ago

I got these errors Msg 156, Level 15, State 1, Line 404 Incorrect syntax near the keyword 'Select'. Msg 102, Level 15, State 1, Line 407 Incorrect syntax near ')'.

|

Gordon Linoff · Accepted Answer · 2015-05-06 20:38:09Z

1

Your syntax looks more like Oracle; SQL Server syntax is quite different. For instance, there is no new: and old:. Instead, there are tables, inserted and deleted.

If I understand correctly, you want to remove one item from the orders in the delete statement. Then, if the last item has been removed, you want to delete the order itself. I think the following does this:

CREATE TRIGGER trg_orders_delete_one_item ON orders
    INSTEAD OF DELETE    
AS
begin
    with todelete  as (
        select oi.*, row_number() over (partition by OrderId order by ItemNumber desc) as seqnum
        from Order_Items oi
        where oi.OrderId in (select OrderId from deleted)
    )
    delete todelete
        where seqnum = 1;

    with todelete as (
          select o.*
          from orders o
          where o.OrderId in (select OrderId from deleted)
         )
    delete todelete
        where not exists (select 1
                          from order_item oi
                          where oi.OrderId = todelete.OrderId
                         )
end;

answered May 6, 2015 at 20:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Muhannad Over a year ago

Thank you very much the query didn't work because it says that ORDER has FK with CUSTOMER ( a table I didn't' mention) .

Muhannad Over a year ago

Thank you very much for you answer again, and I'm really sorry for not showing all my tables that was my bad .

Collectives™ on Stack Overflow

Using triggers to implement referential integrity actions (SQL Server)

2 Answers 2

Update

9 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Update

9 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related