68.关联部门流水号

/*--自己做编号的示例:
	根据输入的RoleID,另一个表中查到一个部门ＩＤ（三位）
	然后用部门ＩＤ作为插入记录流水号的前三位，后面六位为自动增加的。
--*/

--测试环境

--部门表
create table 部门(部门id char(3),部门名称 varchar(10),RoleID int)
insert 部门 
select '001','A部门',1
union all select '002','B部门',2
union all select '003','c部门',3

--A表
create table A表(编号 char(9) primary key default '',RoleID int)
go

--处理的函数
create function f_getid(@RoleID int)
returns char(9)
as
begin
	declare @re char(9),@部门id char(3)

	select @部门id=部门id from 部门 where RoleID=@RoleID
	select @re=max(编号) from A表
	where 编号 like @部门id+'%'
	return(@部门id+case when @re is null then '000001' 
		else right('000000'+cast(cast(right(@re,6) as int)+1 as varchar),6) end)
end
go

--创建触发器,自动生成编号
create trigger t_insert on A表
instead of insert
as
declare @部门编号 char(3),@id int,@RoleID int,@编号 char(9)

select * into #t from inserted order by RoleID

update #t set 
	@编号=case RoleID when @RoleID then @编号 else dbo.f_getid(RoleID) end
	,@部门编号=case RoleID when @RoleID then @部门编号 else left(@编号,3) end
	,@id=case RoleID when @RoleID then @id+1 else right(@编号,6) end
	,编号=@部门编号+right('000000'+cast(@id as varchar),6)
	,@RoleID=RoleID
insert into A表 select * from #t
go

--插入数据到A表
insert A表(RoleID)
select 1
union all select 1
union all select 2
union all select 3
union all select 2
union all select 1
union all select 2
union all select 3
union all select 3
go

--显示处理结果
select * from A表
go

--删除测试环境
drop table 部门,A表
drop function f_getid

/*--测试结果

编号        RoleID      
--------- ----------- 
001000001 1
001000002 1
001000003 1
002000001 2
002000002 2
002000003 2
003000001 3
003000002 3
003000003 3

（所影响的行数为 9 行）
--*/