一个触发器

*设计一个触发器，当删除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]