table A:
id int 自增
psid varchar
type varchar
table B
psid varchar
type varchar
要求給A insert寫一個觸發器
每插入一條記錄,如果psid 為null或者''或者在B裏面找不到這個psid,就不做操作,否則修改psid,type(從B裏取)注意每次插入操作只插入psid最好用create trigger t as update from ...
id int 自增
psid varchar
type varchar
table B
psid varchar
type varchar
要求給A insert寫一個觸發器
每插入一條記錄,如果psid 為null或者''或者在B裏面找不到這個psid,就不做操作,否則修改psid,type(從B裏取)注意每次插入操作只插入psid最好用create trigger t as update from ...
不是多余吗?只修改type就可以了。最简单的就是
update tablea
set type=tableb.type
from tableb
where tablea.id in (select id from inserted) and tablea.psid=tableb.psid and tablea.psid<>'' and tablea.psid is not null
只是一个思路,没有测试
instead of insert
as
begin
insert into A(psid,type)
select
i.psid,isnull(b.type,i.type)
from
inserted i
left join
b
on
i.psid=b.psid
end
go
from A, B
where A.psid=B.psid
我理解为,只要在B里面找的到这个PSID,才操作
create trigger t on A
for insert
as
if (select count(1) from inserted where psid in(select distinct psid from A))>0
begin
update A set A.type=B.type
from A, B
where A.psid=B.psid
end
go
create trigger t on A
for insert
as
if (select count(1) from inserted where psid in(select distinct psid from B))>0
begin
update A set A.type=B.type
from A, B
where A.psid=B.psid
end
go