急急！求购单号自动形成的触发器？？？在线待待！

[dzy983]

例如有表tab（a varchar(100),b varchar(100)）想每次向这张表插入记录时自动形成单号(sj+当前日期+五位流水号)：求购instead of 触发器如何实现。

表内容如下：
a b
sj-2004-06-06-00001 324324
sj-2004-06-06-00002 33343
sj-2004-06-07-00001 22222
sj-2004-09-09-00001 24324
例如:
insert into tab values('sj','2222')执行自动得到单号然后把记录插入到表中去；记录内容应该为：sj-2004-07-10-00001 2222
在线等待！高手指教！！！！！！！！

[netcoder]

--测试一
create table tab(a varchar(100),b varchar(100))
go
create trigger trg_test on tab
instead of insert
as
declare @s varchar(21)
set @s=cast((select isnull(max(cast(right(a,5) as int)),0)+1 from tab)as varchar)
set @s=right('0000' + @s,5)
set @s='-' + convert(varchar(10),getdate(),120)+ '-' + @s
insert tab
select a+@s, b from inserted

go

insert into tab values('sj','dd')
insert into tab values('sj','dd')
select * from tab

drop table tab

--结果
/*--
（所影响的行数为 1 行）

a b
--------------------- ----
sj-2004-07-10-00001 dd
sj-2004-07-10-00002 dd

（所影响的行数为 2 行）
--*/

[zjcxc]

--触发器
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

[zjcxc]

--示例

--表
create table tab(a varchar(100),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-100001 33343
dj-2004-07-100002 24324
sj-2004-07-100001 2222
sj-2004-07-100002 324324
sj-2004-07-100003 33343
sj-2004-07-100004 24234

（所影响的行数为 6 行）
--*/

[dzy983]

别外我有可能存在一次批量插入多条记录！也就是可能触发器一次生成多个单号！
别外还一种情况：
insert into tab ('ww','eeee')要得到插入如下记录：
ww-2004-07-10-00001 eeee
前面的"sj"与"ww"是动态的是不确定的！

[dzy983]

to: zjcxc(邹建) 大哥
sid_new=1000001+isnull(max(cast(right(a.a,5) as int)),0)
为什么用1000001加是什么意思！你能把思想给我说说吗！你sql我有点看不懂！我是sql初学者！知道你能不能帮解释一下！万分感谢！！！！我知道在这儿你很历害！

[dzy983]

我sql初学者！能不能把这一段解释一下：
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

[zjcxc]

--触发器少写了一个-,编码格式有点问题

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

[zjcxc]

用1000001加的目的是生成格式化的编码方便,即可以直接用right(@a,5),就得到形如:
00001,00002这样的编号

[laker_tmj]

up

[lovvver]

对于你上面的问题,sql 语句是直接嵌入的话,不必要用instead of触发器来做,可以直接写一个函数,在插入的时候调用函数直接插入.
该表最好多加一个单号的字段.

[lovvver]

不知你用的什么开发语言,如果是用中间键的话,可以写一个函数来实现.

[8536105]

瑞星公司的单据流水号生成存储过程

CREATE procedure dbo.GetDOCNO(@LEVEL1 tinyint, @DOCNO char(12) output) as
begin
--(2位凭证)XX(1位年)Y(2位月)MM(2位日)DD(2位店铺00-FF)XX(3位流水)001
--{@Level1 一级凭证的类型}
--{@DocNo 凭证号码的返回值}
--生成凭证的单据号码
declare @SECTION varchar(80)
declare @CODE varchar(80)
declare @NAME varchar(80)
declare @SNO varchar(4)
declare @FisrtC char
declare @IsInc bit
declare @NetCode int

begin tran

select @NetCode = dbo.GetIntVar('系统参数','网点编号',0)
select @SECTION = '单据流水号', @CODE = '[' + right('000'+convert(varchar(3), @LEVEL1), 2) + ']' + dbo.GetEBKDSC(@LEVEL1 * 1000000)
if not exists(select 1 from syscfg where [section]= @SECTION and [code] = @CODE)
begin
select @NAME = dbo.GetEBKDSC(@LEVEL1 * 1000000) + '流水号'
insert into syscfg ([section], code, [name], value, issystem, tpid, editformat, sectionname, directinput)
values (@SECTION, @CODE, @NAME, '000', 1, 1, '', @SECTION, 0)
end

select @SNO = Value from SYSCFG where [section] = @SECTION and [code] = @CODE
if len(@SNO) < 3
select @SNO = right('000'+@SNO,3)

if right(@SNO,3) = '999'
select @IsInc = 1
else
select @IsInc = 0

select @SNO = right('000'+convert(varchar(4),cast(right(@SNO,3) as int)+1),3)

update syscfg set value = @SNO where [section] = @SECTION and [code] = @CODE

commit tran

select @DOCNO = right('00' + convert(varchar(3), @LEVEL1), 2) +
right(convert(varchar(8), getdate(), 112), 5) +
right('00'+ dbo.SysInt2Hex(@NetCode), 2) +
right('000'+ @SNO, 3)
select 1
end

[lenghuazhang]

create trigger fortable on tab for insert
as
declare @astr varchar(100),@bstr varchar(100),@cstr varchar(100)
declare @dstr varchar(100),@tempstr varchar(100),@e int
select @astr=convert(varchar(20),getdate(),112)
select @bstr=''
select @bstr=@bstr+'-'+left(@astr,4)+'-'+substring(@astr,5,2)+'-'+substring (@astr,7,2)
select @astr=a from inserted
select @astr=@astr+@bstr
select @cstr=@astr+'%'
if not exists(select * from tab where a like @cstr)
update tab set a=rtrim(@astr)+'-00001'
else
begin
select @dstr=max(a) from tab where a like @cstr
select @e=cast(right(rtrim(@dstr),5) as int)
select @tempstr=case
when len(@e)=1 then '0000'+cast(@e as varchar(1))
when len(@e)=2 then '000'+cast(@e as varchar(2))
when len(@e)=1 then '00'+cast(@e as varchar(3))
when len(@e)=1 then '0'+cast(@e as varchar(4))
else cast(@e as varchar(5))
end
update tab set a=rtrim(@astr)+'-'+@tempstr where a in(select a from inserted)
end
go

试试我的,在我的机子上完全通过测试.
假设表中原有如下数据.
a b
'sj-2004-07-10-00103','djosw'
'sj-2004-07-10-00206','djows'
'sdwj-2004-06-05-00503','djows'
'dfs-2004-07-10-00023','djowsw'

插入测试:
insert into tab
select 'sj','djowsd'
union
select 'sdwj','12345'
union
select 'dfs','djwosw'
union
select 'asdf','23451'

得出的结果为
a b
'sj-2004-07-10-00103','djosw'
'sj-2004-07-10-00206','djows'
'sj-2004-07-10-00207','djowsd'
'sdwj-2004-06-05-00503','djows'
'sdwj-2004-07-10-00001','12345'
'dfs-2004-07-10-00023','djowsw'
'dfs-2004-07-10-00024','djwose'
'asdf-2004-07-10-00001','23451'

[dzy983]

to:lenghuazhang(未名)
你为什么不用instead of insert触发器呢？

[dzy983]

to: lenghuazhang(未名)
你试过这种情况吗：
insert into tab
select 'sj','djowsd'
union all
select 'sj','12345'

[dzy983]

to: zjcxc(邹建)
你可能水平太高！我是sql初学者，你写的我看不懂！

[frankwong]

邹建的好像复杂了点，朱二的够用了

[dzy983]

to： lenghuazhang(未名)
update tab set a=rtrim(@astr)+'-00001'你这句把所有记录都更新啦！

[dzy983]

朱二的处理不了我说的这些情况！

[dzy983]

朱二的如果遇到进行批量进行插入时就不行啦！