22,209
社区成员
发帖
与我相关
我的任务
分享
if object_id ( 'T' ) is not null
drop table T
go
create table T(id varchar (20) primary key ,name varchar ( 10 ))
go
if OBJECT_ID('tr_T_insert') is not null
drop trigger tr_T_insert
go
-- 用触发器完成
create trigger tr_T_insert on T
instead of insert
as
begin
declare @i int ,@dt varchar(8)
set @dt=convert(varchar(8),getdate(),112)
select * into # from inserted
select
@i = isnull (max (right(id,4)), 0 )
from T
where left(ID,8)=@dt
update #
set id = @dt+right ( 10000 + @i, 4 ), @i = @i + 1
insert T
select * from #
end
go
-- 测试
insert T (Name)
select 'a' union all
select 'b'
select * from T
/*
id name
201111190001 a
201111190002 b
*/