--自動編號的例子.項目編號=各項目獨立流水號/各年不同

--創建自定義函數,得到新的ID
create function f_getid(
@年份 char(4),
@項目名稱 varchar(10)
)returns int
as
begin
	declare @re int

	select @re=case when id is null then 1 else cast(@re as int)+1 end
	from(
		select id=max(項目編號) from 表 where 年份=@年份 and 項目名稱=@項目名稱
	) a
	return(@re)
end
go

--創建測試表
create table 表(年份 char(4),項目編號 char(4)
	,項目名稱 varchar(10),項目內容 varchar(100))
go

--創建觸發器,自動生成項目編號
create trigger t_insert on 表
instead of insert
as
select * into #t from inserted order by 年份,項目名稱
declare @年份 char(4),@項目名稱 varchar(10),@id int

update #t set @id=case when @年份=年份 and @項目名稱=項目名稱
		then @id+1 else dbo.f_getid(年份,項目名稱) end
	,項目編號=right('0000'+cast(@id as varchar),4)
	,@年份=年份,@項目名稱=項目名稱
insert into 表 select * from #t
go

--創建觸發器,在刪除/修改時,重新排序項目編號
create trigger t_delete_update on 表
after delete,update
as
declare @年份 char(4),@項目名稱 varchar(10),@id int

declare tb cursor local for
	select 年份,項目名稱 from deleted 
	union
	select 年份,項目名稱 from inserted
open tb
fetch next from tb into @年份,@項目名稱
while @@fetch_status=0
begin
	set @id=0
	update 表 set @id=@id+1
		,項目編號=right('0000'+cast(@id as varchar),4)
	where 年份=@年份 and 項目名稱=@項目名稱
	fetch next from tb into @年份,@項目名稱
end
close tb
deallocate tb
go

--插入數據測試
insert into 表(年份,項目名稱,項目內容)
select '2003','A項目','A-1期'
union all select '2003','A項目','A-2期'
union all select '2004','A項目','A-3期'
union all select '2003','B項目','B-1期'
union all select '2004','B項目','B-11期'
union all select '2003','A項目','A-12期'
union all select '2004','A項目','A-12期'
union all select '2003','B項目','B-12期'
union all select '2004','B項目','B-13期'

--顯示結果
select * from 表 order by 年份,項目名稱
go

--更新測試
update 表 set 年份=2003 where 項目編號='0001' and 項目名稱='A項目'

--顯示結果
select * from 表 order by 年份,項目名稱
go

--刪除部分記錄
delete from 表 
where (年份=2003 and 項目編號='0002') or (年份=2004 and 項目編號='0001')
--顯示結果
select * from 表 order by 年份,項目名稱

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

/*--測試結果

1.插入時,自動編號的效果

年份   項目編號 項目名稱       項目內容  
---- ---- ---------- ------------------
2003 0001 A項目        A-1期
2003 0002 A項目        A-2期
2003 0003 A項目        A-12期
2003 0001 B項目        B-12期
2003 0002 B項目        B-1期
2004 0001 A項目        A-3期
2004 0002 A項目        A-12期
2004 0001 B項目        B-13期
2004 0002 B項目        B-11期


2.修改時,自動編號的效果

年份 項目編號 項目名稱       項目內容  
---- ---- ---------- ------------------
2003 0001 A項目        A-1期
2003 0002 A項目        A-2期
2003 0003 A項目        A-12期
2003 0004 A項目        A-3期
2003 0001 B項目        B-12期
2003 0002 B項目        B-1期
2004 0001 A項目        A-12期
2004 0001 B項目        B-13期
2004 0002 B項目        B-11期


3.刪除時,自動編號的效果

年份   項目編號 項目名稱       項目內容 
---- ---- ---------- -----------------
2003 0001 A項目        A-1期
2003 0002 A項目        A-12期
2003 0003 A項目        A-3期
2003 0001 B項目        B-12期
2004 0001 B項目        B-11期
--*/