Keeping a history of row changes
The Fresh Fruit Delivery company needs to track changes made to the Customers table.
You are asked to create a new trigger that covers any statements modifying rows in the table.
This exercise is part of the course
Building and Optimizing Triggers in SQL Server
Exercise instructions
- Create a new trigger called
CopyCustomersToHistory. - Attach the trigger to the
Customerstable. - Fire the trigger when rows are added or modified.
- Extract the information from the
insertedspecial table.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Create a trigger to keep row history
CREATE TRIGGER ___
ON ___
-- Fire the trigger for new and updated rows
AFTER ___, UPDATE
AS
INSERT INTO CustomersHistory (CustomerID, Customer, ContractID, ContractDate, Address, PhoneNo, Email, ChangeDate)
SELECT CustomerID, Customer, ContractID, ContractDate, Address, PhoneNo, Email, GETDATE()
-- Get info from the special table that keeps new rows
FROM ___;