i am building a shopping cart. I want to update the UNIT_IN_STOCK column in database after order have been submitted. i want to subtract the quanity value from the order made from the UNIT_IN_STOCK column in database. how would the sql statement be like?? i tried this but it didnt work. any suggestions??

CREATE PROCEDURE update_Products_By_name
(

@ProductName varchar,
@UnitInStock int

)
AS

UPDATE Products
SET UnitInStock=(UnitInStock-@UnitInStock)
WHERE ProductName = @ProductName
GO


rrr

__________________
rrr

What is the field type of 'UnitInStock'?

Om Prakash

UnitInStock is type int

rrr

Then i think you should check for @ProductName column, the product which u are passing is same or not (I mean check for spaces in the beginning or end for product name).

Om Prakash

SET UnitInStock=(UnitInStock-@UnitInStock)

is the UnitInStock= UnitInStock - @UnitInStock a valid SQL statement??
(kinda like a = a-b in C++)

rrr

UnitInStock= UnitInStock - @UnitInStock
is a valid statement.

Another possibility could be null in UnitInStock column. If current value in UnitInStock column is null, then it will not update.



Om Prakash

Hi Olambe,

I would realize such by putting a insert trigger on the order(details) table and use the inserted table available within the trigger to update the UNIT_IN_STOCK from the products table.

You can even extend the business rule you created this way by marking the trigger for update and and have it in/decrement the UNIT_IN_STOCK acoording to the difference between inserted/delete "table" once you update any order(detail).

See Books online for more info on triggers.

Regards,

Jago

you can try this as om_prakash stated
CREATE TRIGGER dbo."UpdateQuantity" ON dbo.Order_Details
FOR INSERT
Not for replication
AS
UPDATE dbo.products SET
dbo.products.UnitsSold=inserted.Quantity + inserted.Quantity
FROM inserted
where dbo.products.productID=inserted.productID

Here I used unitsSold and left the unitsinstock for my starting Inventory. When I need to do an inventory I can evalaute and
compare units sold verses units I start with.