Create a Transaction That Rolls Back If Stock Is Insufficient
Create a Transaction That Rolls Back If Stock Is Insufficient
Products Table:
Query Description:
This transaction checks whether there is enough stock available for a product before processing an order.
-
If stock is sufficient, it updates the stock and inserts the order.
-
If stock is insufficient, it rolls back the transaction, preventing any changes to the database.
SQL Query:
GO
BEGIN TRANSACTION;
DECLARE @ProductID INT = 1;
DECLARE @OrderQuantity INT = 10;
DECLARE @CurrentStock INT;
-- Get current stock for the product
SELECT @CurrentStock = UnitsInStock
FROM Products
WHERE ProductID = @ProductID;
-- Check stock availability
IF @CurrentStock >= @OrderQuantity
BEGIN
-- Deduct stock
UPDATE Products
SET UnitsInStock = UnitsInStock - @OrderQuantity
WHERE ProductID = @ProductID;
-- Insert order detail (example values used)
INSERT INTO OrderDetails (OrderID, ProductID, Quantity)
VALUES (1001, @ProductID, @OrderQuantity);
COMMIT TRANSACTION;
PRINT 'Order processed successfully.';
END
ELSE
BEGIN
ROLLBACK TRANSACTION;
PRINT 'Transaction rolled back: Insufficient stock.';
END;
Output:
