Tiggers and Constraints

[Guest]

I have the following position, in SQL server 2000:
Table: Products
Table: Orders
Table: Order Details
Orders and order details are joined thru order id and cascading for update
and delete are enabled.
Products and Order details are joined thru ItemNo.
I have a trigger on orderdetails for delete to update stock in products and
everything is working fine.
Now if I delete the order from orders table the corresponding records are
deleted from order details without updating the corresponding stock.
does anyone have answer for this.

 

[Stefan Hoffmann]

hi,

I have the following position, in SQL server 2000:
Table: Products
Table: Orders
Table: Order Details
Orders and order details are joined thru order id and cascading for update
and delete are enabled.
Products and Order details are joined thru ItemNo.
I have a trigger on orderdetails for delete to update stock in products and
everything is working fine.
Now if I delete the order from orders table the corresponding records are
deleted from order details without updating the corresponding stock.
does anyone have answer for this.

Normally you can calculate this on the fly, if you store a start amount
in Products (this is not a nice accounting, but it works).

Otherwise check your trigger, the following will work:

CREATE TRIGGER [dbo].[tr_OrderDetail_Del]
ON [dbo].[OrderDetail]
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
UPDATE Product
SET Product.StockAmount = Product.StockAmount + Deleted.Amount
FROM Deleted
WHERE Deleted.Product_ID = Product.ID
END

CREATE TRIGGER [dbo].[tr_OrderDetail_Ins]
ON [dbo].[OrderDetail]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE Product
SET Product.StockAmount = Product.StockAmount - Inserted.Amount
FROM Inserted
WHERE Inserted.Product_ID = Product.ID
END


mfG
--> stefan <--