使用触发器来实现引用完整性操作（SQL Server） [英] Using triggers to implement referential integrity actions (SQL Server)

问题描述

我正在尝试为我的表实现一些触发器

I'm trying to implement some triggers for my tables

其中两个表是 ORDERS （订单）和 ORDERS_ITEMS （每个订单都有商品），并且我想在没有更多订单时删除 ORDER ITEMS 在该 ORDER 中。

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;

我不确定如何在SQL Server中编写它，我从书中获得了算法，但是我无法在SQL Server上运行它。

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.

推荐答案

请勿为此使用触发器。在级联删除中使用前键。

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

对不起，我跳错了答案。如果要在没有任何商品连接的情况下删除订单，则级联删除将无济于事。

在order_item表上删除后使用triger进行删除，然后在该表中写入以下删除语句：（再次更新）

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

崩溃：

1. 内部联接与<一个href = https://msdn.microsoft.com/zh-cn/library/ms191300.aspx rel = nofollow> 已删除 表将确保您仅删除 OrderNumber 与从 order_item 表中删除的
   记录相同的记录。


2. 左加入 order_item 表的$ c>以及 where 子句可确保您仅从 orders 表中删除记录附有 order_item 记录。



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. 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.

这篇关于使用触发器来实现引用完整性操作（SQL Server）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！