else
begin
if(@forur_id=0)--三级部门
begin
--一级部门
--首先对当前的部门的一级部门进行筛选
select @rec_num= COUNT(*) from tld_card.dbo._group
--先插入一级部门
insert tld_card.dbo._group(id,lev,id_top,id_sec,id_thi,name,tname)
select @rec_num+1 ,
0,
@rec_num+1,
@rec_num+1 ,
@rec_num+1 ,
i.DPNAME1,
i.DPNAME1
from inserted i
where i.DPNAME1 NOT in (select name from tld_card.dbo._group where tld_card.dbo._group.lev=0)
--二级部门
declare @san_topid bigint
select @rec_num= COUNT(*) from tld_card.dbo._group
select @san_topid=tld_card.dbo._group.id from tld_card.dbo._group,inserted i
where tld_card.dbo._group.name=i.DPNAME1
insert tld_card.dbo._group(id,lev,id_top,id_sec,id_thi,name,tname)
select @rec_num+1 ,
1,
@san_topid,
@rec_num+1 ,
@rec_num+1 ,
i.DPNAME2,
i.DPNAME1+' '+i.DPNAME2
from inserted i
where i.DPNAME2 NOT in (select name from tld_card.dbo._group where tld_card.dbo._group.lev=1)
--
declare @oneid bigint
declare @twoid bigint
select @rec_num= COUNT(*) from tld_card.dbo._group
select @oneid=tld_card.dbo._group.id from tld_card.dbo._group,inserted i
where tld_card.dbo._group.name=i.DPNAME1
select @twoid=tld_card.dbo._group.id from tld_card.dbo._group,inserted i
where tld_card.dbo._group.name=i.DPNAME2
select @rec_num+1 ,
2,
@oneid,
@twoid ,
@rec_num+1,
i.DPNAME3,
i.DPNAME1+' '+i.DPNAME2+' '+i.DPNAME3
from inserted i
where i.DPNAME3 NOT in (select name from tld_card.dbo._group where tld_card.dbo._group.lev=2)
end
else --四级部门
begin
---先一级部门
select @rec_num= COUNT(*) from tld_card.dbo._group
--先插入一级部门
insert tld_card.dbo._group(id,lev,id_top,id_sec,id_thi,name,tname)
select @rec_num+1 ,
0,
@rec_num+1,
@rec_num+1 ,
@rec_num+1 ,
i.DPNAME1,
i.DPNAME1
from inserted i
where i.DPNAME1 NOT in (select name from tld_card.dbo._group where tld_card.dbo._group.lev=0)
---二级部门
declare @four_topid bigint
select @rec_num= COUNT(*) from tld_card.dbo._group
select @four_topid=tld_card.dbo._group.id from tld_card.dbo._group,inserted i
where tld_card.dbo._group.name=i.DPNAME1
insert tld_card.dbo._group(id,lev,id_top,id_sec,id_thi,name,tname)
select @rec_num+1 ,
1,
@four_topid,
@rec_num+1 ,
@rec_num+1 ,
i.DPNAME2,
i.DPNAME1+' '+i.DPNAME2
from inserted i
where i.DPNAME2 NOT in (select name from tld_card.dbo._group where tld_card.dbo._group.lev=1)
---三级部门
declare @four_oneid bigint
declare @four_twoid bigint
select @rec_num= COUNT(*) from tld_card.dbo._group
select @four_oneid=tld_card.dbo._group.id from tld_card.dbo._group,inserted i
where tld_card.dbo._group.name=i.DPNAME1
select @four_twoid=tld_card.dbo._group.id from tld_card.dbo._group,inserted i
where tld_card.dbo._group.name=i.DPNAME2
select @rec_num+1 ,
2,
@four_oneid,
@four_twoid ,
@rec_num+1,
i.DPNAME3,
i.DPNAME1+' '+i.DPNAME2+' '+i.DPNAME3
from inserted i
where i.DPNAME3 NOT in (select name from tld_card.dbo._group where tld_card.dbo._group.lev=2)
--四级部门
declare @si_oneid bigint
declare @si_twoid bigint
declare @si_sanid bigint
select @rec_num= COUNT(*) from tld_card.dbo._group
select @si_oneid=tld_card.dbo._group.id from tld_card.dbo._group,inserted i
where tld_card.dbo._group.name=i.DPNAME1
select @si_twoid=tld_card.dbo._group.id from tld_card.dbo._group,inserted i
where tld_card.dbo._group.name=i.DPNAME2
select @si_sanid=tld_card.dbo._group.id from tld_card.dbo._group,inserted i
where tld_card.dbo._group.name=i.DPNAME3
select @rec_num+1 ,
3,
@si_oneid,
@si_twoid ,
@si_sanid,
i.DPNAME4,
i.DPNAME1+' '+i.DPNAME2+' '+i.DPNAME3+' '+i.DPNAME4
from inserted i
where i.DPNAME4 NOT in (select name from tld_card.dbo._group where tld_card.dbo._group.lev=3)
--
end
end
if(@sec_id=0 and @third_id=0 and @forur_id=0)--一级部门
begin
select @rec_num= COUNT(*) from tld_card.dbo._group
insert tld_card.dbo._group(id,lev,id_top,id_sec,id_thi,name,tname)
select @rec_num+1 ,
0,
@rec_num+1 ,
@rec_num+1 ,
@rec_num+1,
i.DPNAME1,
i.DPNAME1
from inserted i
where i.DPNAME1 NOT in (select name from tld_card.dbo._group where tld_card.dbo._group.lev=0)
end
else
begin ------二级部门
if( @third_id=0 and @forur_id=0)
begin
--首先对当前的部门的一级部门进行筛选
select @rec_num= COUNT(*) from tld_card.dbo._group
--先插入一级部门
insert tld_card.dbo._group(id,lev,id_top,id_sec,id_thi,name,tname)
select @rec_num+1 ,
0,
@rec_num+1,
@rec_num+1 ,
@rec_num+1,
i.DPNAME1,
i.DPNAME1
from inserted i
where i.DPNAME1 NOT in (select name from tld_card.dbo._group where tld_card.dbo._group.lev=0)
--二级部门
declare @topid bigint
select @rec_num= COUNT(*) from tld_card.dbo._group
select @topid=tld_card.dbo._group.id from tld_card.dbo._group,inserted i
where tld_card.dbo._group.name=i.DPNAME1
insert tld_card.dbo._group(id,lev,id_top,id_sec,id_thi,name,tname)
select @rec_num+1 ,
1,
@topid,
@rec_num+1 ,
@rec_num+1 ,
i.DPNAME2,
i.DPNAME1+' '+i.DPNAME2
from inserted i
where i.DPNAME2 NOT in (select name from tld_card.dbo._group where tld_card.dbo._group.lev=1)
end
--判断级别
select @one_strid=DPCODE1 from inserted
select @sec_strid=DPCODE2 from inserted
select @third_strid=DPCODE3 from inserted
select @forur_strid=DPCODE4 from inserted
set @one_id=CAST(@one_strid AS bigint)
set @sec_id=CAST(@sec_strid AS bigint)
set @third_id=CAST(@third_strid AS bigint)
set @forur_id=CAST(@forur_strid AS bigint)
-------------------------------------------------------------
--先获取当前记录数目
declare @rec_num bigint