The management of AdventureWorks, Inc. has decided that no user should be able to change the prices of the products. In addition, management wants that all the attempts to change the price should be saved in a temporary table, Temp. John, the database developer has been asked to make the significant changes in the database to implement this policy. What can John do to achieve the same? | Instructor Inputs Session 15 Solutions to Exercises This session includes exercises of Chapter 8 Chapter 9 and Chapter 10. Chapter 8 Exercise 1 The management of AdventureWorks Inc. has decided that no user should be able to change the prices of the products. In addition management wants that all the attempts to change the price should be saved in a temporary table Temp. John the database developer has been asked to make the significant changes in the database to implement this policy. What can John do to achieve the same Solution To accomplish this task John needs to create an update trigger on the ProductCostHistory table. In addition he also needs to create a temporary table to store the changes in it. To create the temporary table John needs to execute the following statements in the SQL Server Management Studio CREATE TABLE Temp ProductID int AttChangeCost money AttTime datetime To create the update trigger on the ProductCostHistory John needs to execute the following statements CREATE TRIGGER updTrigger ON Production . ProductCostHistory INSTEAD OF UPDAtE AS BEGIN DECLARE @PID AS int DECLARE @COST AS money SELECT @pid ProductID @cost StandardCost FROM Deleted INSERT INTO Temp VALUES @pid @cost getdate SELECT Sorry you can not change the price of a Product END NIIT Instructor Inputs .
