表Aid1, id2, name
S021 wang
S022 li
s023 zhang
当进行一个操作后(这个操作不能更改),就会把A表name 插入到B中,同时自动生成B.id2 段,同时回写A表的id2.如下
表B
id2 name
T0001 wang
T0002 li
T0003 zhang我现在想做一个触发器,让插入B表的自动生成的id2改成 A表的 id1这个触发器怎么写??
S021 wang
S022 li
s023 zhang
当进行一个操作后(这个操作不能更改),就会把A表name 插入到B中,同时自动生成B.id2 段,同时回写A表的id2.如下
表B
id2 name
T0001 wang
T0002 li
T0003 zhang我现在想做一个触发器,让插入B表的自动生成的id2改成 A表的 id1这个触发器怎么写??
create table tb(id1 varchar(10), name varchar(10))alter trigger test_tr on dbo.ta
for insert
as
begin
declare @i int
select * into # from inserted
select @i=isnull(cast(right(max(id1),4)as int),0) from tb
update # set id1='T'+right('0000'+rtrim(@i),4),@i=@i+1
insert tb select id1,name from #
update ta set id2=#.id1 from # where ta.name=#.name and
exists(select 1 from inserted where id1=ta.id1)
end
insert ta(id1,name) select 'S021', 'wang'
union all select 'S022', 'li'
union all select 's023', 'zhang'select * from ta
select * from tb
create table ta(id1 varchar(10), id2 varchar(10), name varchar(10))
create table tb(id1 varchar(10), name varchar(10))create trigger test_tr on dbo.ta
for insert
as
begin
declare @i int
select * into # from inserted
select @i=isnull(cast(right(max(id1),4)as int),0) from tb
update # set id1='T'+right('0000'+rtrim(@i),4),@i=@i+1
insert tb select id1,name from #
endalter trigger test_tr2 on dbo.tb
for insert
as
begin
update ta set id2=tb.id1 from ta join tb on tb.name=ta.name
where exists(select 1 from inserted where id1=tb.id1)
and ta.id2 is null
endinsert ta(id1,name) select 'S021', 'wang'
union all select 'S022', 'li'
union all select 's023', 'zhang'select * from ta
select * from tb--drop table ta,tb
id之间的转换不用管的!无论B表生成的id2是多少我们不管,
我只要作个触发器,让B.id2=A.id1
FOR INSERT
ASSET NOCOUNT ONinsert into tab1 (id2)
select id2
from inserted
where id2=inserted.id2
FOR INSERT
ASSET NOCOUNT ONupdate tab1
set
id2=(select id2 from inserted where usename=inserted.usename)
where usename=(select usename from inserted where usename=inserted.usename)
CREATE TRIGGER [trig_updateid] ON [dbo].[tab2]
FOR INSERT
ASSET NOCOUNT ONupdate B set id2=A.id1 from inserted ,A ,B
where a.name=b.name and B.id2=inserted.id2update A set id2=a.id1 from A,inserted where ....好像就是这样的,不知道对不对,大家看看