下面是两个表,,表里code相同,,但是两个表没有关联,,,我想要更新表A的同时表B里code相对应的值也同时更新,,我是新手,希望高手能帮忙写出详细命令。
---------------------------------------------------------------------
A
CODE TIME DATE DATA
5000 19:53:40 2008-09-10 6
0027 20:23:58 2008-09-24 7
5027 20:16:28 2008-09-27 8
----------------------------------------------------------------------
B
CODE DATA TIME DATE
5000 20 14:37:34 2008-09-01
5000 10 08:02:17 2008-07-10
0027 20 12:05:59 2008-09-24
0027 20 10:03:21 2008-08-01
0027 10 08:00:10 2008-08-10
5027 20 11:07:00 2008-09-27
--------------------------------------------------------------------
---------------------------------------------------------------------
A
CODE TIME DATE DATA
5000 19:53:40 2008-09-10 6
0027 20:23:58 2008-09-24 7
5027 20:16:28 2008-09-27 8
----------------------------------------------------------------------
B
CODE DATA TIME DATE
5000 20 14:37:34 2008-09-01
5000 10 08:02:17 2008-07-10
0027 20 12:05:59 2008-09-24
0027 20 10:03:21 2008-08-01
0027 10 08:00:10 2008-08-10
5027 20 11:07:00 2008-09-27
--------------------------------------------------------------------
for update
as
update b
set DATA=i.DATA, TIME=i.TIME, DATE=i.DATE
from inserted as i join b
on b.CODE=i.CODE
表A建立触发器:create trigger trig_[表A]_upd on dbo.[表A]
for update
as
if update(date)
begin
update b set b.date=a.date,b.TIME=a.TIME,b.data=a.data from [表A] a,[表B] b where a.CODE=b.CODE
end
for update
as update b set b.date=a.date,b.TIME=a.TIME,b.data=a.data from [表A] a,[表B] b where a.CODE=b.CODE
create trigger Tupcod on a
for update
as
if update(code)
begin
update B set code=d.code
from B,inserted d
where B.code=d.code
end
if @@error<>0
begin
rollback
return
end
return