--自動編號的例子.材料編號=類別編號+流水號

--創建自定義函數,得到新的ID
create function f_getid(
@類別編號 varchar(3))
returns int
as
begin
	declare @re int
	select @re=right(id,4) from(
		select id=max(材料編號) from tb where 類別編號=@類別編號
	) a
	set @re=isnull(@re,0)+1
	return(@re)
end
go

--創建測試表
create table tb(材料編號 varchar(7) primary key default '',類別編號 varchar(3),材料名稱 varchar(10))
go

--創建觸發器,自動生成材料編號
create trigger t_insert on tb
instead of insert
as
select * into #t from inserted order by 類別編號
declare @類別編號 varchar(3),@id int
update #t set @id=case when @類別編號=類別編號 then @id+1 else dbo.f_getid(類別編號) end
	,材料編號=類別編號+right('0000'+cast(@id as varchar),4)
	,@類別編號=類別編號
insert into tb select * from #t
go

--插入數據測試
insert into tb(類別編號,材料名稱)
select '101','A材料'
union all select '101','B材料'
union all select '302','C材料'

--顯示結果
select * from tb order by 材料編號

go
--刪除測試環境
drop table tb
drop function f_getid