两张表主表:drug_info,从表: cpa_drug。其中主表的pzwh和从表的batch_no字段相关联。主表的pzwh字段改变的时候从表中的bathc_no字段也要改变,并且当主表xh_code的字段改变的时候从表中frug_no的字段也要改变,保留没有改变的数据!怎么写触发器啊?我写了个存储过程,不过,查询速度无法令我满意!查了十多分钟都没查出来!各位救救我吧!
create procedure pro_gxcd
asupdate testcpa_drug
set testcpa_drug.drug_code=testdrug_info.xh_code
from testdrug_info
where testdrug_info.pzwh=testcpa_drug.standard_idgo
create procedure pro_gxcd
asupdate testcpa_drug
set testcpa_drug.drug_code=testdrug_info.xh_code
from testdrug_info
where testdrug_info.pzwh=testcpa_drug.standard_idgo
create trigger tr_drug_info on drug_info
after update
as
if @@rowcount>1
begin
raiserror 50001 N'操作失败'
rollback tran
return
end
if update(drug_code) or update(frug_no)
update c
set c.drug_code=i.drug_code,c.xh_code=i.frug_no
from inserted i ,deleted d ,cpa_drug c
where d.drug_code=c.drug_code and c.xh_code=d.frug_no