Question paper (build 07.05.20.19)
9
9/2023
1
347000.00
10
10/2023 2
11
11/2023 1
2111000.00
525000.00
12
12/2023 0
0.00
Picture 8.1
Question 9:
Create a trigger named trg_Update TotalAmount on the OrderDetails table to ensure the
TotalAmount column in the Orders table always stays accurate.
Whenever an OrderDetails record is inserted, updated, or deleted, the trigger should
automatically recalculate the total amount of the affected orders as the sum of (Quantity x
Price) for all its items. If all OrderDetails of an order are deleted, the total amount of this
order should be set to 0.
For example, if you run the following queries on the table OrderDetails, the corresponding
orders will be updated.
-- test case 1 -> TotalAmount of the orders with OrderID = 3 and OrderID = 5 will be updated
INSERT OrderDetails (OrderID, ItemID, Quantity, Price)
VALUES (3, 2, 3, 75000.00), (5,27,2,40000.00)
-- test case 2 -> TotalAmount of the order with OrderID = 6 will be updated
