create table test_f (id char(5), b int) go drop trigger tri_test_f go create trigger tri_test_f on test_f instead of insert as begin declare @maxid int declare @newid char(5) select @maxid = isnull (max(convert(int,id)),0) from test_f select identity(int,1,1) x, * into #tmp from inserted insert test_f select substring (convert(char(7),(x+@maxid)*0.00001),3,7) ,b from #tmp end go
create trigger tri_test_f on test_f instead of insert as begin declare @maxid int select @maxid = isnull (max(convert(int,id)),0) from test_f select identity(int,1,1) x, * into #tmp from inserted insert test_f select substring (convert(char(7),(x+@maxid)*0.00001),3,7) ,b from #tmp end go
create trigger tri_test_f on test_f instead of insert as begin declare @id int declare @newid char(5) declare @b int declare my_cursor cursor for select b from inserted open my_cursor fetch next from my_cursor into @b while @@fetch_status = 0 begin select @id = isnull(min(a+1),1) from (select convert(int,id) as a from test_f union select 0 as a from test_f) t where not exists (select 1 from test_f where t.a+1 =convert(int,test_f.id)) select @newid = substring (convert(char(7),@id*0.00001),3,7) insert test_f (id ,b) values (@newid,@b) fetch next from my_cursor into @b end close my_cursor deallocate my_cursor end go呵呵,和补缺那个一样了
go
drop trigger tri_test_f
go
create trigger tri_test_f on test_f instead of insert
as
begin
declare @maxid int
declare @newid char(5)
select @maxid = isnull (max(convert(int,id)),0) from test_f
select identity(int,1,1) x, * into #tmp from inserted
insert test_f select substring (convert(char(7),(x+@maxid)*0.00001),3,7) ,b from #tmp
end
go
as
begin
declare @maxid int
select @maxid = isnull (max(convert(int,id)),0) from test_f
select identity(int,1,1) x, * into #tmp from inserted
insert test_f select substring (convert(char(7),(x+@maxid)*0.00001),3,7) ,b from #tmp
end
go
as
begin
declare @id int
declare @newid char(5)
declare @b int
declare my_cursor cursor for select b from inserted
open my_cursor
fetch next from my_cursor into @b
while @@fetch_status = 0
begin
select @id = isnull(min(a+1),1) from (select convert(int,id) as a from test_f union select 0 as a from test_f) t where not exists (select 1 from test_f where t.a+1 =convert(int,test_f.id))
select @newid = substring (convert(char(7),@id*0.00001),3,7)
insert test_f (id ,b) values (@newid,@b)
fetch next from my_cursor
into @b
end
close my_cursor
deallocate my_cursor
end
go呵呵,和补缺那个一样了