首先申明用触发器可能会出现重复号码的情况,必须有办法限制。
create trigger tr_test_insert
on test
for insert
as
if (select count(*) from inserted )>1 returndeclare @date varchar(12)
select @date=convert(varchar(12),getdate(),112)if exists (select * from test where xh like @date+'%')
begin
update test
set xh=@date+right('000'+cast(cast((select right(max(xh),4) from test where xh like @date+'%') as int)+1 as varchar(4)),4)
from test,inserted
where test.key=inserted.keyend
else
update test
set xh=@date+'0001'
from test,inserted
where test.key=inserted.key
create trigger tr_test_insert
on test
for insert
as
if (select count(*) from inserted )>1 returndeclare @date varchar(12)
select @date=convert(varchar(12),getdate(),112)if exists (select * from test where xh like @date+'%')
begin
update test
set xh=@date+right('000'+cast(cast((select right(max(xh),4) from test where xh like @date+'%') as int)+1 as varchar(4)),4)
from test,inserted
where test.key=inserted.keyend
else
update test
set xh=@date+'0001'
from test,inserted
where test.key=inserted.key
解决方案 »
- Liang145小朋友问个非技术性的问题..
- 这么晚了请求老师们帮助,一个变量传递问题
- 请问如何修改以下的update语句?
- 从excel导数据到MS SQLServer遇到的问题
- 关于 mysql 字段值截取
- 注册的远程服务器不支持参数怎么办?
- 求一条sql(高手帮下忙)
- "超出了存储过程,函数、触发器或视图的最大嵌套层数(最大层数为32)"是什么错误?,
- 存储过程的问题
- 有熟SQL sever7的吗?帮哦看一下
- 我在ACCESS中建的库结构,如何迁移到SQLSERVER中?导入倒是可以,但里面的查询都变成了表,我想直接把它变为视图,请问各位有没有什么好
- 请问如何将两个统计的数据集JOIN?
我也看不懂这个key是什么意思,是另外加的一个字段吗
for insert
as
begin
declare @xh varchar(12)
if exists
(select 1 from tablename where left(xh,8)=convert(varchar(8),getdate(),112))
begin
select @xh=max(right(xh,4)) from tablename where left(xh,8)=convert(varchar(8),getdate(),112)
set @xh=convert(varchar(8),getdate(),112)+right('000'+cast(cast(@xh as int)+1 as varchar(4)),4)
end
else
begin
set @xh=convert(varchar(8),getdate(),112)+'0001'
end
update tablename set xh=@xh where xh='99999'
end语句:
insert tablename ('99999',........)