CommencerCommencer gratuitement

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 the TotalAmount as Quantity * Price. This is done using a computed column which uses columns from the same table for the calculation.

  • The trigger SalesCalculateTotalAmount was created on the SalesWithoutPrice table. The Price column is not part of the SalesWithoutPrice table, so a computed column cannot be used for the TotalAmount. The trigger overcomes this limitation by using the Price column from the Products table.

Cet exercice fait partie du cours

Building and Optimizing Triggers in SQL Server

Afficher le cours

Exercice interactif pratique

Essayez cet exercice en complétant cet exemple de 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;
Modifier et exécuter le code