Click here to Skip to main content
15,851,453 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
I have a table called sales.cancellation which is like
SQL
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

SQL
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
SQL
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
"
SQL
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?
Posted

1 solution

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

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


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
 
Share this answer
 
Comments
Amol_B 8-Oct-13 0:57am    
perfect +5
Rakshith Kumar 8-Oct-13 0:57am    
Thanks For ur solution Madhu.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900