--处理的函数
create function f_getid(@类别 varchar(10))
returns char(5)
as
begin
declare @re char(5),@id varchar(10)
select @id=缩写 from 表1 where 类别=@类别
select @re=max(编号) from 表2
where 编号 like @id+'%'
return(@id+case when @re is null then '001'
else right('000'+cast(cast(right(@re,3) as int)+1 as varchar),3) end)
end
go
--创建触发器,自动生成编号
create trigger t_insert on 表2
instead of insert
as
declare @类别 varchar(10),@缩写 varchar(10),@id int,@编号 char(5)
select * into #t from inserted order by 类别
update #t set
@编号=case 类别 when @类别 then @编号 else dbo.f_getid(类别) end
,@缩写=case 类别 when @类别 then @缩写 else left(@编号,2) end
,@id=case 类别 when @类别 then @id+1 else right(@编号,3) end
,编号=@缩写+right('000'+cast(@id as varchar),3)
,@类别=类别
insert into 表2 select * from #t
go
--插入数据到A表
insert 表2(类别,型号)
select '鼠标','双飞燕3D'
union all select '鼠标','罗技'
union all select '键盘','太阳花'
go