*设计一个触发器，当删除Employees中的某个雇员时，删除Orders表中的与这个雇员*/ 


/*相关的一切记录。*/ 


/*********** 第一步、创建存储过程 ***********/ 


drop Trigger Employees_Delete 


go 


Create Trigger Employees_Delete 


On Employees 


INSTEAD OF Delete --此处必须用INSTEAD OF如果采用For 或UPDATE模式，系统会报错，详见SQL参考 


As 


Begin 


 --删除雇员前必须先删除参照该雇员的所有关系里的纪录 


 --1、先删除订单明细表里，该雇员所下订单的明细 


 Delete [Order Details] From Orders Inner Join Deleted On Orders.EmployeeId=Deleted.EmployeeId 


 where Orders.OrderID=[Order Details].OrderID 




 --2、再删除订单表里，该雇员所下的订单 


 Delete Orders From Deleted 


 where Orders.EmployeeId=Deleted.EmployeeId 



 --3、删除EmployeeTerritories表里该雇员的信息 


 Delete EmployeeTerritories From Deleted 


 where EmployeeTerritories.EmployeeId=Deleted.EmployeeId 



 --4、删除雇员表里该雇员的信息，由于前面使用的是INSTEAD OF模式，此处必须有，否则雇员表里的数据没有被删除 


 Delete Employees From Deleted 


 where Employees.EmployeeId=Deleted.EmployeeId 


End 


Go 



/*********** 第二步、执行删除操作，比较删除前后各表记录数的变化 ***********/ 


--1、查看删除数据前各表的记录数 


select count(*) from Employees 


select count(*) from EmployeeTerritories 


select count(*) from Orders 


select count(*) from [Order Details] 



--2、执行删除9号雇员的命令 


delete Employees where EmployeeId =9 



--3、查看删除数据前各表的记录数 


select count(*) from Employees 


select count(*) from EmployeeTerritories 


select count(*) from Orders 


select count(*) from [Order Details]