Instructor Inputs - Session 15

Instructor Inputs 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 15.3 To verify the update trigger on the ProductCostHistory, John needs to execute the following statements: UPDATE [Production].[ProductCostHistory] SET StandardCost = 55 WHERE ProductID = 707 Database engine will display the following message: ‘Sorry you can not change the price of a Product’ Exercise 2 The management of AdventureWorks, Inc. wants that whenever the pay rate of an employee is modified, its effect on the monthly salary of the employee should be displayed. John, a database developer at AdventureWorks, has been asked to resolve this problem. Help John to find out appropriate solution. Tip Monthly Salary = Rate * PayFrequency * 30 Solution To accomplish this task, John needs to create an update trigger on the HumanResources.EmployeePayHistory. To create the trigger, he needs to execute the following statements: CREATE TRIGGER updTrigger ON HumanResources.EmployeePayHistory FOR UPDATE AS BEGIN DECLARE @rate AS money DECLARE @frq AS int SELECT @rate = Rate, @frq = PayFrequency FROM Inserted SELECT @rate * @frq * 30 AS `Monthly Salary` END 15.4 Instructor Inputs ¤NIIT To verify the trigger, John needs to execute the following statement to update the salary for employee with EmployeeID as 160: UPDATE HumanResources.EmployeePayHistory SET Rate = Rate + 5 WHERE EmployeeID = 160 The output will show the monthly salary after the change is done. Chapter 9 Exercise 1 The AdventureWorks database maintains the sales details in the SalesOrderHeader and SalesOrderDetails tables. As a database developer of AdventureWorks, Inc., you need to retrieve all the order numbers for the sales accounts in the following format: AccountNumber 10-4020-000676 10-4020-000117 OrderNumbers SO43659 SO44305 SO45061 SO45779 SO43660 SO47660 SO49857 SO51086 How will you retrieve data in the given format? Note To complete this exercise, you need to provide the AccontDetails.dll file to the students. The file is available in the Datafiles_for_students\QMDS2005\Chapter 09 folder in the TIRM CD. Solution To solve the preceding problem, you need to perform the following tasks: 1. Create the assembly. 2. Create the function. 3. Verify the result. ¤NIIT Instructor Inputs 15.5 ... - tailieumienphi.vn