删除内部插入触发器 [英] Deleting inside Insert trigger

问题描述

我有一个名为sales.cancellation的表，就像

I have a table called sales.cancellation which is like

create table sales.cancellation(
cancellationid int identity(1,1) primary key,
salesid int foreign key references sales.details(salesid)
)

我的要求是，当我输入order.cancellation的值时，必须取消订单，shop.stock表必须在订单生成前显示原始值。还要我需要删除sales.details表中的特定条目。我有一个名为shop.stock的表，就像这样

My requirement is, when i enter the value to the order.cancellation then the order must be cancelled and the shop.stock table must show the original value before the order was made.Also i need to delete that particular entry in the sales.details table. I have a table called shop.stock which is like this

create table shop.stock(
shopid int foreign key references shop.details(shopid),
itemid int foreign key references item.item(itemid),
stock remaining int
)

对于上面提到的要求我在sales.details上使用insert for insert作为

for the above mentioned requirement i use trigger for insert on sales.details as

create trigger [sales].[trigoncancel] on [sales].[cancellation]
for 
insert
as
declare @salesid int;
declare @itemid int;
declare @storeid int;
declare @quantity int;
select  @salesid= salesid from inserted;
set @itemid=(select sales.details.itemid from sales.details where sales.details.salesid=@salesid);
set @storeid=(select sales.details.shopid from sales.details where sales.details.salesid=@salesid);
set @quantity=(select sales.details.quantity from sales.details where sales.details.salesid=@salesid);
update shop.stock
set stock_remaining=stock_remaining+@quantity where shopid=@storeid and itemid=@itemid;
delete from sales.details where sales.details.salesid=@salesid;

当我向sales.cancellation插入一个值时，我收到类似这样的错误

when i insert a value to sales.cancellation i get an error something like this
"

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__cancellat__sales__71D1E811". The conflict occurred in database "onlinesshop", table "sales.details", column 'salesid'.

为什么我会收到此错误？如何解决这个问题？

"
why do i get this error? How to solve this problem?

推荐答案

你已经写完了从sales.details表中删除记录的查询。

You have written query for deleting record from sales.details table at the end.

delete from sales.details where sales.details.salesid=@salesid;

除非首先从sales.cancellation中删除相应的记录，你将无法从sales.details表中删除记录，因为你在sales.cancellation和sale.details之间创建了一个外键引用。



如果您仍想从sales.details表中删除，则删除sales.cancellation表中的外键约束

Unless you delete the corresponding record from sales.cancellation first, you won't be able to delete record from sales.details table as you have created a foreign key reference between sales.cancellation and sale.details.

In case you still want to delete from sales.details table then drop the foreign key constraint in sales.cancellation table

这篇关于删除内部插入触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！