Microsoft SQL Server 2008 R2 Unleashed- P104:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 974 CHAPTER 30 Creating and Managing Triggers --update the data related to the jobs table UPDATE jobs SET FROM inserted i WHERE AND OR OR --update the data related to the employee table UPDATE employee SET FROM inserted i WHERE GO A section in Listing checks the fields related to the jobs table and updates the base table if any of the values have changed. Another section updates the employee table for the employee fields that have been changed in the view. NOTE You could enhance the trigger in Listing to include logic to check for specific updates or to update only those employees who are assigned to the job and have a job level below the new minimum. These enhancements are not included in the listing to keep the example simple. If you now execute the same UPDATE statement you don t get an error message UPDATE employeeJobs SET min_lvl 40 job_lvl 40 WHERE job_id 12 GO The following results show values selected from the employeeJobs view after the update is executed successfully min_lvl max_lvl job_id job_desc job_lvl emp_id 100 14 Designer 35 ENL44273F 25 100 14 Designer 89 PSA89086M Download from Using DML Triggers 975 25 100 14 Designer 100 KFJ64308F 25 100 13 Sales Representative 35 PMA42628M 25 100 13 Sales Representative 64 CGS88322F 25 100 13 Sales Representative 100 TPO55093M 40 100 12 Editor 40 Y-L77953M 40 100 12 Editor 40 H-B39728F 40 100 12 Editor 40 HAS54740M Notice that the Editor job now has a minimum level min_lvl equal to 40 and that all the employees who have that job level job_lvl are also set to 40. You can see the added flexibility that you get by using the INSTEAD OF trigger on a basic view. This flexibility is also applicable to a more sophisticated view called a distributed partitioned view. With this .
