--根據輸入類別自動生成編號示例

--表
create table tab(a varchar(20),b varchar(100))
go

--觸發器
create trigger tr_insert on tab
instead of insert
as
declare @dt varchar(10)
set @dt='-'+convert(varchar(10),getdate(),120)

select * into #t 
from inserted a join(
	select gid_new=b.a,sid_new=1000001+isnull(max(cast(right(a.a,5) as int)),0)
	from tab a 
		right join inserted b on charindex(b.a+@dt,a.a)=1
	group by b.a
)b on a.a=b.gid_new
order by b.gid_new

declare @nid varchar(100),@a int
update #t set @a=case @nid when gid_new then @a+1 else sid_new end
	,a=gid_new+@dt+'-'+right(@a,5)
	,@nid=gid_new

insert tab select a,b from #t
go

--插入數據
insert tab values('sj','2222')
insert tab  select 'sj','324324'
union  all  select 'sj','33343'
union  all  select 'dj','33343'
union  all  select 'dj','24324'
union  all  select 'sj','24234'

--顯示插入結果
select * from tab order by a
go

--刪除測試
drop table tab

/*--測試結果

a                    b       
-------------------- --------
dj-2004-07-1-00001   33343
dj-2004-07-1-00002   24324
sj-2004-07-1-00001   2222
sj-2004-07-1-00002   324324
sj-2004-07-1-00003   33343
sj-2004-07-1-00004   24234

（所影響的行數為 6 行）
--*/