我刚才试着写了一个不知道对不对,大家看一下行不行,给批评一下create trigger TG_test_id
on test
for INSERT
as
update t set id=(select item_value from d_sys_dict where item_type='系统编号' and item_name='图库编号')
from test t inner join inserted i on i.id=t.id
update d_sys_dict set item_value=item_value+1 where item_type='系统编号' and item_name='图库编号'
on test
for INSERT
as
update t set id=(select item_value from d_sys_dict where item_type='系统编号' and item_name='图库编号')
from test t inner join inserted i on i.id=t.id
update d_sys_dict set item_value=item_value+1 where item_type='系统编号' and item_name='图库编号'
update t set id=(select Max(item_value ) from ...
create table 表A(
id int not null primary key default 0, --写个默认值,这样插入时可以不指定该字段值
name varchar(20) not null,
[desc] varchar(50))create table 表B(seq int not null)
go--处理的触发器
create trigger tr_insert on 表A
instead of insert
as
declare @i int,@r int
select @i=seq from 表B
if @@rowcount=0 select @i=0,@r=1
select * into #t from inserted
update #t set @i=@i+1,id=@i
insert 表A select * from #t
if @r=1
insert 表B select @i
else
update 表B set seq=@i
go--插入单条记录测试
insert 表A(name,[desc]) values('aa','bb')--插入多条记录测试
insert 表A(name,[desc])
select 'bb','cc'
union all select 'dd','ee'
go--显示结果
select * from 表A
select * from 表B
go--删除测试
drop table 表A,表B/*--测试结果id name desc
----------- -------------------- ----------
1 aa bb
2 bb cc
3 dd ee(所影响的行数为 3 行)seq
-----------
3(所影响的行数为 1 行)
--*/