Triggers vs. computed columns
While continuing your analysis of the database, you find two other interesting objects:
The table
SalesWithPrice
has a column that calculates theTotalAmount
asQuantity * Price
. This is done using a computed column which uses columns from the same table for the calculation.The trigger
SalesCalculateTotalAmount
was created on theSalesWithoutPrice
table. ThePrice
column is not part of theSalesWithoutPrice
table, so a computed column cannot be used for theTotalAmount
. The trigger overcomes this limitation by using thePrice
column from theProducts
table.
This exercise is part of the course
Building and Optimizing Triggers in SQL Server
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Add the following rows to the table
INSERT INTO ___ (Customer, Product, Price, Currency, Quantity)
VALUES ('Fruit Mag', 'Pomelo', 1.12, 'USD', 200),
('VitaFruit', 'Avocado', 2.67, 'USD', 400),
('Tasty Fruits', 'Blackcurrant', 2.32, 'USD', 1100),
('Health Mag', 'Kiwi', 1.42, 'USD', 100),
('eShop', 'Plum', 1.1, 'USD', 500);
-- Verify the results after adding the new rows
SELECT * FROM SalesWithPrice;