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)
)
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
)
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;
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?