日期:2014-05-18 浏览次数:21399 次
create table test ( SYSID varchar(10), -- varchar字段 name varchar(10) ) create trigger t_insert on test INSTEAD OF insert as declare @id varchar(10),@id1 int select * into #tb from inserted select @id=max(SYSID) from test if @id is null set @id1=0 else set @id1=@id update #tb set @id1=@id1+1 ,SYSID= @id1 insert into test select * from #tb drop table #tb go insert into test(name) select 'aa ' union all select 'bb ' union all select 'cc ' select * from test SYSID name ---------- ---------- 1 aa 2 bb 3 cc (3 行受影响)
------解决方案--------------------
挺有趣的题目,花了点时间,研究了一下。
instead of insert 触发器。
--表 create table test ( id char(10) not null, value char(10) not null, constraint pk_test primary key (id) ) --触发器 create trigger tri_insert on test instead of insert as declare @id int if exists(select top 1 id from test) select @id = max(convert(int,id))+1 from test else select @id=1 INSERT INTO test SELECT @id,value FROM inserted --测试 insert into test (id,value) values ('','a') insert into test (id,value) values ('','b') select * from test