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
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
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 insertedgoinsert into tab values('sj','dd')
insert into tab values('sj','dd')
select * from tabdrop table tab--结果
/*--
(所影响的行数为 1 行)a b
--------------------- ----
sj-2004-07-10-00001 dd
sj-2004-07-10-00002 dd(所影响的行数为 2 行)
--*/
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_newdeclare @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_newinsert tab select a,b from #t
go
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_newdeclare @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_newinsert 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 行)
--*/
别外还一种情况:
insert into tab ('ww','eeee')要得到插入如下记录:
ww-2004-07-10-00001 eeee
前面的"sj"与"ww"是动态的是不确定的!
sid_new=1000001+isnull(max(cast(right(a.a,5) as int)),0)
为什么用1000001加是什么意思!你能把思想给我说说吗!你sql我有点看不懂!我是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
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_newdeclare @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_newinsert tab select a,b from #t
go
00001,00002这样的编号
该表最好多加一个单号的字段.
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'
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
你为什么不用instead of insert触发器呢?
你试过这种情况吗:
insert into tab
select 'sj','djowsd'
union all
select 'sj','12345'
你可能水平太高!我是sql初学者,你写的我看不懂!
update tab set a=rtrim(@astr)+'-00001'你这句把所有记录都更新啦!