create trigger tr_insert on tb
for insert
as
begin
declare @num int,@pid int
select @num=count(pid) from tb
if @num=0
begin
update inserted set pid=1
end
else
begin
select @pid=max(pid) from tb
update inserted set pid=@pid+1
end
end if
select into tb from inserted
end
go
CREATE TRIGGER TEMP_SC on A
INSTEAD OF Insert
as
declare @MAx int
declare @T table (id int IDENTITY (1,1)NOT NULL,T_Test varchar(10))
insert @T select T_Test from inserted
select @MAx=isnull(max(pid),0) from A
insert A select id+@MAx,T_Test from @T
--老大的方法可以处理批量插入,但忘了给变量赋值
create trigger tr_insert on 你的表
instead of insert -- 注意触发器类型
as
if @@rowcount = 0 return
select * into # from inserted
declare @pkid int
select @pkid=isnull(max(pid),0) from 你的表
update # set pid=@pkid, @pkid=@pkid+1
insert 你的表 select * from #
create trigger tr_insert on 你的表
instead of insert -- 注意触发器类型
as
if @@rowcount = 0 return
select * into # from inserted
declare @pkid int
select isnull(max(pid),0) from 你的表
update # set pid=@pkid, @pkid=@pkid+1
insert 你的表 select * from #
--drop trigger tr_autoadd
go
create trigger tr_autoadd
on autoadd
instead of insert
as
begin
declare @m varchar(5)
declare @n int
--select * from inserted
select @m=b from inserted
if (select count(*) from autoadd)=0
begin
insert into autoadd values('00001',@m)
end
else
begin
select @n=max(cast(a as int))+1 from autoadd
insert into autoadd values(replicate('0',5-len(cast(@n as varchar(5))))+cast(@n as varchar(5)),@m)
end
if @@error<>0
begin
rollback tran
end
end