SQL上下级级连操作触发器

/*
　　 1。实现级联查询 例如，提供一个员工编号，能够查询出该
　　 员工的上级和间接上级的信息。
　　 2。实现级联查询 例如，提供一个员工编号，能够查询出该
　　 员工的下级和间接下级的信息。
　　 3。实现级联删除。例如，指定一个员工编号，当删除该员工
　　 的基本信息的时候，该员工的所有下级全部被删除。无论
　　 上级还是下级，都包括员工自己。
　　 4。实现级联更新。当一个员工的工号改变时，所有以该员工
　　 为直接上级的员工的reportto字段的值都变为新的工号。
　　*/
　　
　　create table emp
　　(
　　eid int primary key,
　　ename varchar(20),
　　sal money,
　　reportto int references emp(eid) --该员工的直接上级
　　)
　　--drop table emp
　　insert into emp select 1001,'rain',1000,null
　　union select 1002,'ann',3000,1001
　　union select 1003,'lopez',2000,1001
　　union select 1004,'nakata',3000,1002
　　union select 1005,'tae',1500,1004
　　union select 1006,'raul',900,1004
　　union select 1007,'owen',15000,1006
　　--select * from emp
　　/*实现级联查询 例如，提供一个员工编号，能够查询出该员工的上级和间接上级的信息。*/
　　create proc p1 @eid int
　　as
　　begin
　　 declare @re table(eid int,level int)
　　 declare @l int
　　 set @l=0
　　 insert @re select @eid,@l
　　 while @@rowcount>0
　　 --全局变量@@rowcount，记录上次操作影响的行数
　　 begin
　　 set @l=@l+1
　　 insert @re select a.reportto,@l from emp as a,@re as b where a.eid=b.eid and b.level=@l-1
　　 --循环将当前记录的直接下级插入@re
　　 end
　　 select a.* from @re as b inner join emp as a on a.eid = b.eid
　　end
　　--drop proc p1
　　exec p1 1005
　　
　　/*实现级联查询 例如，提供一个员工编号，能够查询出该员工的下级和间接下级的信息。*/
　　create proc p2 @eid int
　　as
　　begin
　　 declare @re table(eid int,level int)
　　 declare @l int
　　 set @l=0
　　 insert @re select @eid,@l
　　 while @@rowcount>0
　　 --全局变量@@rowcount，记录上次操作影响的行数
　　 begin
　　 set @l=@l+1
　　 insert @re select a.eid,@l from emp as a,@re as b where a.reportto=b.eid and b.level=@l-1
　　 --循环将当前记录的直接上级插入@re
　　 end
　　 --select * from @re
　　 select a.* from @re as b inner join emp as a on a.eid = b.eid
　　end
　　--drop proc p2
　　exec p2 1004
　　
　　
　　/*实现级联删除。*/
　　--例如，指定一个员工编号，当删除该员工
　　--的基本信息的时候，该员工的所有下级全部被删除。无论
　　--上级还是下级，都包括员工自己。
　　create proc p3 @eid int
　　as
　　begin
　　 delete from emp where eid=@eid
　　end
　　--drop proc p3
　　
　　create trigger emp_del
　　on emp instead of delete
　　as
　　begin
　　 declare @eid int
　　 if (not exists (select * from sysobjects where name='del'))
　　 begin
　　 select * into del from emp where 1>2
　　 end
　　 --如触发器第一次执行，则创建del表
　　 insert into del select * from deleted
　　 declare cr cursor scroll for select eid from del
　　 open cr
　　 fetch last from cr into @eid
　　
　　
　　 declare @re table(eid int,level int)
　　 declare @l int
　　 set @l=0
　　
　　 close cr
　　 deallocate cr
　　 insert @re select @eid,@l
　　 while @@rowcount>0
　　 --全局变量@@rowcount，记录上次操作影响的行数
　　 begin
　　 set @l=@l+1
　　 insert @re select a.eid,@l from emp as a,@re as b where a.reportto=b.eid and b.level=@l-1
　　 --循环将当前记录的直接下级插入@re
　　 end
　　 --select * from @re
　　 --select a.* from @re as b inner join emp as a on a.eid = b.eid
　　 --delete emp where eid in (select eid from @re)
　　 set @l=(select max(level) from @re)
　　 --将最下级的level值赋值给@l
　　 while @l>=0
　　 begin
　　 delete emp from emp as a inner join @re as b on a.eid =b.eid and b.level=@l
　　
　　 set @l=@l-1
　　 --自最下级自下而上的删除记录，否则将被外键约束终止
　　 end
　　end
　　--drop trigger emp_del
　　exec p3 1002
　　select * from emp
　　
　　/*实现级联更新。当一个员工的工号改变时，所有以该员工
　　 为直接上级的员工的reportto字段的值都变为新的工号。
　　*/
　　create proc p4 @eid int,@newid int
　　--@eid修改的员工号，@newid修改后的员工号
　　as
　　begin
　　 update emp set eid=@newid where eid=@eid
　　
　　end
　　--drop proc p4
　　
　　
　　create trigger emp_update
　　on emp instead of update
　　as
　　begin
　　 if(update(eid))
　　 begin
　　 declare @eid int,@newid int
　　 --@eid修改的员工号，@newid修改后的员工号
　　 if (not exists (select * from sysobjects where name='olddata'))
　　 begin
　　 select * into olddata from emp where 1>2
　　 end
　　 if (not exists (select * from sysobjects where name='newdata'))
　　 begin
　　 select * into newdata from emp where 1>2
　　 end
　　 insert into olddata select * from deleted
　　 insert into newdata select * from inserted
　　 declare cr1 cursor scroll for select eid from olddata
　　 open cr1
　　 fetch last from cr1 into @eid
　　 declare cr2 cursor scroll for select eid from newdata
　　 open cr2
　　 fetch last from cr2 into @newid
　　
　　 close cr1
　　 deallocate cr1
　　 close cr2
　　 deallocate cr2
　　 rollback
　　 --回退，否则emp表将认为新记录为重复键...具体原理不知道，蒙出来的
　　 --select * from emp
　　 insert into emp select * from newdata where eid=@newid
　　 --插入新记录，与更新后的记录相同
　　 update emp set reportto=@newid where reportto=@eid
　　 --将其直接下级的上级更新为新纪录
　　 delete emp where eid=@eid
　　 --删除原记录
　　 end
　　end
　　--drop trigger emp_update
　　--select * from newdata
　　exec p4 1004,1008
　　--参数2应为原表中不存在的员工号，避免重复键
　　select * from emp