Q6BjJgg5

1IF OBJECT_ID('tempdb..#stock') IS NOT NULL DROP TABLE #stock;
2IF OBJECT_ID('tempdb..#basket') IS NOT NULL DROP TABLE #basket;
3GO
4
5SET NOCOUNT ON;
6
7
8CREATE TABLE #stock (
9    product varchar(50) PRIMARY KEY,
10    quantity int
11);
12
13INSERT INTO #stock 
14VALUES 
15     ('pants', 30),
16     ('shirt', 18),
17     ('shoes', 4);
18
19
20CREATE TABLE #basket (
21    product varchar(50),
22    quantity int
23);
24
25
26-- At this point the stock covers the basket
27INSERT INTO #basket 
28VALUES 
29    ('pants', 2),
30    ('shoes', 4);
31
32-- Uncomment this statement to make it fail
33INSERT INTO #basket 
34VALUES 
35    ('shoes', 1);
36
37
38
39
40DECLARE @invalid_stock TABLE (
41    product varchar(50) PRIMARY KEY,
42    quantity int
43);
44
45
46BEGIN TRAN
47
48INSERT INTO @invalid_stock
49SELECT * 
50FROM (
51    UPDATE s
52    SET quantity = s.quantity - b.quantity
53    OUTPUT inserted.*
54    FROM #stock AS s
55    INNER JOIN (
56        SELECT product, SUM(quantity) AS quantity
57        FROM #basket 
58        GROUP BY product
59    ) AS b
60        ON s.product = b.product
61) AS newstock
62WHERE quantity < 0;
63
64
65IF EXISTS (SELECT * FROM @invalid_stock)
66BEGIN
67    ROLLBACK;
68    THROW 50001, 'Not enough stock to complete this order!', 1;
69END
70ELSE
71BEGIN 
72    COMMIT;
73    PRINT 'Transaction complete!'
74END