SQL Transaction Help:

I have 4 SQL tables (noted in detail below). How can I create a SQL transaction that would create a new order, add a product to that order and then deduct the order quantity from the quantity on hand (QOH) - all to occur only if the quantity left over after the transaction would still be greater than zero?

Tables:

Product

• SKU Int (auto-generating)
• Product Name nchar
• QOH int

Order

• Order # - int (auto-generating)
• Customer ID int

Customer

• Customer ID int (auto-generating)
• Customer Name nchar

OrderItem

• Order Item ID int (auto-generating)
• Order # - int
• SKU int
• Qty - int