if object_id('tb') is not null drop table tb go create table tb(id int,name varchar(50)) create trigger tri_name on tb instead of insert as begin begin tran declare @maxid int select @maxid=isnull(max(id),0)+1 from tb with(tablock) insert into tb(id,name) select @maxid,name from inserted commit tran endinsert into tb(name) select 'aa' insert into tb(name) select 'bb' insert into tb(name) select 'cc'select * from tbid name 1 aa 2 bb 3 cc
set nocount on create table tb(id int,n int,v int) go create trigger t_tb on tb instead of insert as begin declare @id int select * into # from inserted select @id = max(id) from tb select @id=isnull(@id,0) update # set id = @id,@id = @id + 1 insert tb select * from # end go insert tb(n,v) select 10,10 insert tb(n,v) select 20,20 insert tb(n,v) select 1,2 union select 3,3 union select 4,4 select * from tb go drop table tb /* id n v ----------- ----------- ----------- 1 10 10 2 20 20 3 1 2 4 3 3 5 4 4 */
现在的情况原来是自增值的列变为了int 还不允许null ,所以原来的插入都出错, 我只想用触发器实现原来自增的功能, 不想改动太多,能解决吗?
drop table tb
go
create table tb(id int,name varchar(50))
create trigger tri_name
on tb
instead of insert
as
begin
begin tran
declare @maxid int
select @maxid=isnull(max(id),0)+1 from tb with(tablock)
insert into tb(id,name) select @maxid,name from inserted
commit tran
endinsert into tb(name) select 'aa'
insert into tb(name) select 'bb'
insert into tb(name) select 'cc'select * from tbid name
1 aa
2 bb
3 cc
create table tb(id int,n int,v int)
go
create trigger t_tb on tb
instead of insert
as
begin
declare @id int
select * into # from inserted
select @id = max(id) from tb
select @id=isnull(@id,0)
update # set id = @id,@id = @id + 1
insert tb select * from #
end
go
insert tb(n,v) select 10,10
insert tb(n,v) select 20,20
insert tb(n,v) select 1,2 union select 3,3 union select 4,4
select * from tb
go
drop table tb
/*
id n v
----------- ----------- -----------
1 10 10
2 20 20
3 1 2
4 3 3
5 4 4
*/