/ *
1. For example, an employee number, to be able to query the information of the employee’s supervisor and indirect superior cascade query.
2. For example, an employee number to check out the employee’s subordinates and indirect subordinates cascade query.
3. Cascade delete. For example, specifying an employee number, all subordinate staff were all deleted when delete the employee’s basic information. Whether superiors or subordinates, including staff.
4. Cascading updates. When an employee’s job number is changed, all the employees immediate supervisor the staff reportto field values ??into a new job number.
* /
create table emp
(
eid int primary key,
ename varchar (20),
sal money,
reportto int references emp (eid) – the employee’s direct supervisor)
– 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
/ * Cascade query For example, an employee number, to check out the employee’s supervisor and indirect superior. * /
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
– Global variable @ @ rowcount, recording the last operation affects the number of rows 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
– Loop current record directly subordinate insert @ 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
/ * Cascade query For example, an employee number, be able to query the employee’s subordinates and indirect subordinates. * /
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
– Global variable @ @ rowcount, recording the last operation affects the number of rows 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
– Cycle the immediate superior of the current record into @ 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
/ * Cascade delete. * /
– Specify, for example, an employee number, all subordinate staff were all deleted when the employee is removed – the basic information. No matter – the higher or lower levels, including staff.
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
– Such as the trigger for the first time you run, create del table 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
– Global variable @ @ rowcount, recording the last operation affects the number of rows 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
– Loop current record directly subordinate insert @ 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)
– The most subordinate level value assigned to _AT_ 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
– Delete records from the lowest level bottom-up, otherwise it will be a foreign key constraint to terminate the end
end
– Drop trigger emp_del
exec p3 1002
select * from emp
/ * Cascading updates. When an employee’s job number is changed, all the employees immediate supervisor the staff reportto field values ??into a new job number.
* /
create proc p4 @ eid int, @ newid int
– The staff number _AT_ eid modify _AT_ NEWID modify the employee number 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
– Employee number _AT_ eid modify, the staff after _AT_ NEWID modified 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
– Back to back, or the emp table will be a new record for the duplicate key … do not know the specific principles, Mongolian out – select * from emp
insert into emp select * from newdata where eid = @ newid
– Insert a new record, the same with the updated records update emp set reportto = @ newid where reportto = @ eid
– Be directly subordinate superiors updated with the new record delete emp where eid = @ eid
– Delete the original recording end
end
– Drop trigger emp_update
– Select * from newdata
exec p4 1004,1008
– Parameter 2 for the employee number does not exist in the original table, avoid duplicate key select * the FROM emp
