--自動(dòng)編號(hào)的例子.項(xiàng)目編號(hào)=各項(xiàng)目獨(dú)立流水號(hào)/各年不同

--創(chuàng)建自定義函數(shù),得到新的ID
create function f_getid(
@年份 char(4),
@項(xiàng)目名稱(chēng) 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(項(xiàng)目編號(hào)) from 表 where 年份=@年份 and 項(xiàng)目名稱(chēng)=@項(xiàng)目名稱(chēng)
	) a
	return(@re)
end
go

--創(chuàng)建測(cè)試表
create table 表(年份 char(4),項(xiàng)目編號(hào) char(4)
	,項(xiàng)目名稱(chēng) varchar(10),項(xiàng)目?jī)?nèi)容 varchar(100))
go

--創(chuàng)建觸發(fā)器,自動(dòng)生成項(xiàng)目編號(hào)
create trigger t_insert on 表
instead of insert
as
select * into #t from inserted order by 年份,項(xiàng)目名稱(chēng)
declare @年份 char(4),@項(xiàng)目名稱(chēng) varchar(10),@id int

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

--創(chuàng)建觸發(fā)器,在刪除/修改時(shí),重新排序項(xiàng)目編號(hào)
create trigger t_delete_update on 表
after delete,update
as
declare @年份 char(4),@項(xiàng)目名稱(chēng) varchar(10),@id int

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

--插入數(shù)據(jù)測(cè)試
insert into 表(年份,項(xiàng)目名稱(chēng),項(xiàng)目?jī)?nèi)容)
select '2003','A項(xiàng)目','A-1期'
union all select '2003','A項(xiàng)目','A-2期'
union all select '2004','A項(xiàng)目','A-3期'
union all select '2003','B項(xiàng)目','B-1期'
union all select '2004','B項(xiàng)目','B-11期'
union all select '2003','A項(xiàng)目','A-12期'
union all select '2004','A項(xiàng)目','A-12期'
union all select '2003','B項(xiàng)目','B-12期'
union all select '2004','B項(xiàng)目','B-13期'

--顯示結(jié)果
select * from 表 order by 年份,項(xiàng)目名稱(chēng)
go

--更新測(cè)試
update 表 set 年份=2003 where 項(xiàng)目編號(hào)='0001' and 項(xiàng)目名稱(chēng)='A項(xiàng)目'

--顯示結(jié)果
select * from 表 order by 年份,項(xiàng)目名稱(chēng)
go

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

go
--刪除測(cè)試環(huán)境
drop table 表
drop function f_getid

/*--測(cè)試結(jié)果

1.插入時(shí),自動(dòng)編號(hào)的效果

年份   項(xiàng)目編號(hào) 項(xiàng)目名稱(chēng)       項(xiàng)目?jī)?nèi)容  
---- ---- ---------- ------------------
2003 0001 A項(xiàng)目        A-1期
2003 0002 A項(xiàng)目        A-2期
2003 0003 A項(xiàng)目        A-12期
2003 0001 B項(xiàng)目        B-12期
2003 0002 B項(xiàng)目        B-1期
2004 0001 A項(xiàng)目        A-3期
2004 0002 A項(xiàng)目        A-12期
2004 0001 B項(xiàng)目        B-13期
2004 0002 B項(xiàng)目        B-11期


2.修改時(shí),自動(dòng)編號(hào)的效果

年份 項(xiàng)目編號(hào) 項(xiàng)目名稱(chēng)       項(xiàng)目?jī)?nèi)容  
---- ---- ---------- ------------------
2003 0001 A項(xiàng)目        A-1期
2003 0002 A項(xiàng)目        A-2期
2003 0003 A項(xiàng)目        A-12期
2003 0004 A項(xiàng)目        A-3期
2003 0001 B項(xiàng)目        B-12期
2003 0002 B項(xiàng)目        B-1期
2004 0001 A項(xiàng)目        A-12期
2004 0001 B項(xiàng)目        B-13期
2004 0002 B項(xiàng)目        B-11期


3.刪除時(shí),自動(dòng)編號(hào)的效果

年份   項(xiàng)目編號(hào) 項(xiàng)目名稱(chēng)       項(xiàng)目?jī)?nèi)容 
---- ---- ---------- -----------------
2003 0001 A項(xiàng)目        A-1期
2003 0002 A項(xiàng)目        A-12期
2003 0003 A項(xiàng)目        A-3期
2003 0001 B項(xiàng)目        B-12期
2004 0001 B項(xiàng)目        B-11期
--*/