drop table dbo.num_tb
drop table dbo.tb
create table num_tb(d datetime, id int)
insert num_tb
select '2004-01-01', 1
CREATE TABLE [tb]
(
[AutoID] [bigint] IDENTITY(1,1) NOT NULL,
[id] [varchar](20) NULL,
[name] [varchar](10) NULL,
[CreateTime] [datetime] default getdate(),
CONSTRAINT [PK_tb] PRIMARY KEY CLUSTERED
(
[AutoID] ASC
)
) ON [PRIMARY]
create unique nonclustered index idx_id_tb on tb(id)
go
create trigger tri_tb on tb
INSTEAD OF INSERT
as
begin
set nocount on
declare @i int
--,@id varchar(20)
,@j int
select
@i = count(*)
from inserted
begin tran
declare @now varchar(14), @latest varchar(14)
update num_tb with(TABLOCKX)
set
@now = replace(replace(replace(convert(varchar(19), getdate(), 120), '-', ''), ' ',''), ':', ''),
@latest = replace(replace(replace(convert(varchar(19), d, 120), '-', ''), ' ',''), ':', ''),
id = (
case
when @latest = @now
then id + @i
else
@i
end
),
@j = (
case
when @latest = @now
then id
else
0
end
),
d = getdate()
commit tran
select *
into #t
from inserted
update #t
set id = @now
+ right('000000' + rtrim(@j), 6)
,@j = @j + 1
insert tb (id, name)
select id,name
from #t
end
go
--创建表
go
--插入记录测试
declare @i int
set @i=0
while @i< = 1000
begin
insert into tb(name) values('张三')
insert into tb(name)
select '张四' union all
select '张五' union all
select '张六' union all
select '张七' union all
select '张八' union all
select '张九' union all
select '张十'
set @i = @i + 1
end
select MAX(id),MIN(id),COUNT(*),
MAX(AutoID),MIN(AutoID),MAX(CreateTime),MIN(CreateTime)
from tb
group by LEFT(id, 14)
order by 3 desc
select * from tb a
where exists
(select 1 from tb where id > a.id and AutoID < a.AutoID)
--truncate table tb
