多表修改的问题

[eyeon]

table RE
ID orderID myTable myID amount
----------------------------------------------
1 76 A 100 10
2 76 B 201 20

table A
myID amount
------------------------------------------------
100 1

table B
myID amount
------------------------------------------------
201 2


期望结果:
table A
myID amount
------------------------------------------------
100 11

table B
myID amount
------------------------------------------------
201 22

希望能够用一个SQL实现,如果能够帮助解决,可立即结贴
thx!

[xluzhong]

create table tt(id int,sid int,amount int)
insert into tt select 1,100,10 union all select 2,201,20
create table t(aid int,amount int)
insert into t select 100,1
create table t2(bid int,amount int)
insert into t2 select 201,2
go
create trigger tttt
on t
for update
as
update t2
set amount=a.amount+b.amount
from t2 a
inner join tt b
on a.bid=b.sid
go

go

update t
set amount=a.amount+b.amount
from t a
inner join tt b
on a.aid=b.sid
go

select *
from t
select *
from t2

drop table tt
drop table t
drop table t2

[eyeon]

可能我没有说清楚，table RE中的MyID并不是唯一的，必须拿myTable + MyID 才可以去找一个表、并且找到唯一的记录，即存在这样的情况
table RE
ID orderID myTable myID amount
----------------------------------------------
1 76 A 100 10
2 76 B 201 20
3 80 A 100 10

table A
myID amount
------------------------------------------------
100 1

table B
myID amount
------------------------------------------------
201 2


期望结果:
table A
myID amount
------------------------------------------------
100 11

table B
myID amount
------------------------------------------------
201 22

希望能够用一个SQL实现,如果能够帮助解决,可立即结贴
thx!

[zjcxc]

--在 RE 上建立如下触发器实现同步更新A,B表就行了
create trigger tr_process on RE
for insert,update,delete
as

--同步表A
update a set amount=isnull(a.amount,0)+isnull(b.amount,0)
from A ,(
select myID,amount=sum(amount)
from(
select myID,amount from inserted where myTable='A'
union all
select myID,-amount from deleted where myTable='A'
)aa group by myID
)b where a.myID=b.myID

--同步表B
update B set amount=isnull(a.amount,0)+isnull(b.amount,0)
from B ,(
select myID,amount=sum(amount)
from(
select myID,amount from inserted where myTable='B'
union all
select myID,-amount from deleted where myTable='B'
)aa group by myID
)a where a.myID=b.myID

[eyeon]

FT，写错了

应该是
table RE
ID orderID myTable myID amount
----------------------------------------------
1 76 A 100 10
2 76 B 201 20
3 76 C 100 10

[zjcxc]

--看来楼主是想表也自动了,那就这样写处理语句

--在 RE 上建立如下触发器实现同步更新A,B表就行了
create trigger tr_process on RE
for insert,update,delete
as
if @@rowcount=0 return --没有需要处理的记录就直接退出
declare @s nvarchar(4000)

select myTable,myID,amount=sum(amount)
into #i from(
select myTable,myID,amount from inserted
union all
select myTable,myID,amount from deleted
)a group by myTable,myID

declare tb cursor local
for
select N'update a set amount=isnull(a.amount,0)+isnull(i.amount,0)
from '+quotename(myTable)+N' a ,#i i
where a.myID=i.myID and i.myTable='+quotename(myTable,'''')
from #i i
where exists(select * from sysobjects where xtype='U' and name=i.myTable)
group by myTable
open tb
fetch tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch tb into @s
end
close tb
deallocate tb

[xluzhong]

写个存储过程
create proc test
@mytable nvarchar(50),
@myid int
as
exec('update a set amount=a.amount+b.amount from '+@mytable+' a inner join re b on b.'+cast(@myid as nvarchar(20))+'=a.myid'
go