Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Transact-SQL (2000)  Transaction advice - placing order on a ecomm site

Author  Topic  isodope Starting Member 1 Post Posted - 2008-10-17 : 10:45:15 I am in the process of creating a ecommerce solution for a small business. The site will need to take into consideration concurrency issues. I have used SQL Server before but never taken advantage of its Transaction handling. I will really be extremely grateful to anyone who can help with advice and suggestions on whether I am on the right track. I have two tables to hold order information. Orders - holds generic order details Customer Details (email, name etc) Order Value Details (subtotal, tax, discounts, shipping etc) Shipping Details (address info) Billing Details (probably not needed as I will be used a payment gateway such as HSBC or PayPal) OrderItems - holds details of each of the items that make up the order Order - associated order record Stock - associated stock record Title - short product desc UnitPrice - price per stock item Quantity - number of these items ordered I need to write a single proc which will place this order, creating the order record, adding each order item - checking stock levels etc. My proposed logic to this proc is: -- steps: -- 1. create order record - set status to OrderBeingPlaced (OrderStatusID = 5) -- 2. create temp table for holding order items (order items details are passed in a comma-delimted string) -- 3. populate temp table -- foreach item in temp table -- 3. lock stock record for this orderitem -- 4. check stock levels (rollback, unlock stock records and return if stock cant satisfy quantity being ordered) -- 5. add order items () -- 6. update stock levels (rollback, unlock stock records and return if stock cant satisfy quantity being ordered) -- 7. update status to OrderPlaced - commit and return new order id WEBSITE TRYS TO TAKE PAYMENT (using payment processor) -- if successful update status to PaymentRecieved -- if unsuccessful need to call another proc to delete order record, delete order item records and release the stock As a precaution I will also be creating a sql job (scheduled to run every day at some specific time) to check whether there are any Orders with status OrderPlaced for longer than x hours and if so again delete order record, delete order items and release stock. Let me know if there is any information you need to help me. Thanks for taking the time to read this.