1. We need a stored procedure that needs to be executed whenever a customer places an order. The stored procedure checks the existing stock in the inventory db and decides whether the order can be placed or not based on the existing stock and the quantity requested. If the order can be placed, the requested quantity is deducted from the stock and the new stock is updated. 
The structure of the stored procedure is as follows -
1. Name of the stored procedure: _spPlaceOrder
2. Take in productid (id of a product from inventory table) and quantity (a random integer) as parameters
3. Initiate a transaction.
4. Insert a record in Orders table with order_date (current date), productid (as received by the stored procedure), and quantity (as received by the stored procedure).
5. Store the orderId of the newly inserted record in a variable.
6. Fetch the existing quantity and price of the requested product from Inventory table in stock_count column and price column respectively.
7. If existing stock count is equal or more than the quantity requested, deduct the requested quantity from the stock count, update the total column of the current order in Orders table and commit the transaction. Print the message 'Order Placed Successfully.'
8. If existing stock count is less than the quantity requested, rollback the transaction and print the message 'Insufficient stock to fulfil the order.

Answer rating: 100% (QA)

You can create the spPlaceOrder stored procedure in SQL as follows sql DELIMITER CREATE PROCEDURE sp... View the full answer