学号.sql

sqlserver 数据库编程的绝好脚本

--创建自定义函数,得到新的ID
create function f_getid(
@YXDM varchar(3),
@zydm varchar(4)
)
returns int
as
begin
	declare @re int
	
	select @re=right(id,4) from(
		select id=max(id) from tb where yxdm=@yxdm and zydm=@zydm
	) a
	set @re=isnull(@re,0)+1
return(@re)
end
go


--创建测试表
create table tb(id varchar(12)
		,YXDM varchar(3),ZYDM varchar(4))
go

--创建触发器,生成ID号
create trigger t_insert on tb
instead of insert
as
select * into #t from inserted order by yxdm,zydm
declare @yxdm varchar(3),@zydm varchar(4),@id int

update #t set @id=case when @yxdm=yxdm and @zydm=zydm then @id+1 else dbo.f_getid(yxdm,zydm) end
	,id='GY'+right(yxdm,2)+@zydm+right('0000'+cast(@id as varchar),4)
	,@yxdm=yxdm,@zydm=zydm

insert into tb
	select * from #t
go

--插入数据测试
insert into tb(YXDM,ZYDM) values('001','0001')
insert into tb(YXDM,ZYDM) values('001','0001')
insert into tb(YXDM,ZYDM) values('001','0002')
insert into tb(YXDM,ZYDM) values('001','0002')
insert into tb(YXDM,ZYDM) values('001','0001')
insert into tb(YXDM,ZYDM) values('001','0003')
insert into tb(YXDM,ZYDM) values('001','0003')
insert into tb(YXDM,ZYDM) values('002','0001')
insert into tb(YXDM,ZYDM) values('002','0001')
insert into tb(YXDM,ZYDM) values('002','0001')

--显示结果
select * from tb order by id

go
--删除测试环境
drop table tb
drop function f_getid