使用触发器实现参照完整性操作（SQL Server）

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.

其中2个表是ORDERS（有订单）和ORDERS_ITEMS（每个订单都有项目），我想在该ORDER中没有更多项目时删除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.

我不知道如何在SQL Server中编写这个，我从书中得到了算法，但我无法在SQL Server上运行它。

2 个解决方案

#1

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

不要使用触发器。使用带有级联删除的foriegn键。

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)

对不起，我跳到了错误的结论。如果您想在没有连接项目的情况下删除订单，则级联删除将无济于事。在order_item表上使用triger删除后删除并在那里写下删除语句:(再次更新）

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:

分解：

1. 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.
2. 带有已删除表的内部联接将确保您只删除OrderNumber与order_item表中已删除记录相同的记录。
3. 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.
4. 带有order_item表的左连接以及where子句确保只有在没有附加order_item记录的情况下才从orders表中删除记录。

#2

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.

您的语法看起来更像Oracle; SQL Server语法完全不同。例如，没有新的：和旧的：而是有插入和删除的表。

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:

如果我理解正确，您想从delete语句中的订单中删除一个项目。然后，如果删除了最后一项，则要删除订单本身。我认为以下是这样的：

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;