使用触发器来实现引用完整性操作(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
崩溃:
-
内部联接
与<一个href = https://msdn.microsoft.com/zh-cn/library/ms191300.aspx rel = nofollow>已删除
表将确保您仅删除 OrderNumber 与从 order_item 表中删除的
记录相同的记录。 -
左加入 order_item 表的$ c>以及
where
子句可确保您仅从 orders 表中删除记录附有 order_item 记录。
- The
inner join
withdeleted
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 thewhere
clause ensures you only delete records from orders table if they have no order_item records attached to them.
这篇关于使用触发器来实现引用完整性操作(SQL Server)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!