... set @gh11=(select gh from zzryxxb) ...返回多條記錄,系統無法賦值
alter trigger tri_xl_zzryxxb on dbo.zzryxxb for update as if update(d_am2) and update(rxdate2) and update(bydate2) declare @gh11 char(8),@d_am11 char(2),@d_bs11 char(2),@d_ai11 char(6),@rxdate11 datetime,@bydate11 datetime,@byxx21 char(28),@d_an11 char(2) declare @d_am12 char(2),@rxdate12 datetime,@bydate12 datetime set @gh11=(select gh from zzryxxb) --这一句不对--该为set @gh11=(select gh from deleted) 因为select gh from zzryxxb 返回的是多个值 set @d_am11=(select d_am2 from deleted where gh=@gh11) set @d_bs11=(select d_bs2 from deleted where gh=@gh11) set @d_ai11=(select d_ai2 from deleted where gh=@gh11) set @rxdate11=(select rxdate2 from deleted where gh=@gh11) set @bydate11=(select bydate2 from deleted where gh=@gh11) set @byxx21=(select byxx2 from deleted where gh=@gh11) set @d_an11=(select d_an2 from deleted where gh=@gh11) set @d_am12=(select d_am2 from inserted where gh=@gh11) set @rxdate12=(select rxdate2 from inserted where gh=@gh11) set @bydate12=(select bydate2 from inserted where gh=@gh11) if @d_am11<>@d_am12 and @rxdate11<>@rxdate12 and @bydate11<>@bydate12 begin update zzryxxb set d_am1=@d_am11,d_bs1=@d_bs11,d_ai1=@d_ai11, rxdate1=@rxdate11,bydate1=@bydate11,byxx1=@byxx21,d_an1=@d_an11 where gh=@gh11 end
set @gh11=(select gh from zzryxxb) GH值不是一条。。 所以建议游标
感觉好象可以只要一句就搞定,没必要用游标和变量。if update(d_am2) and update(rxdate2) and update(bydate2) update a set d_am1=b.d_am1,d_bs1=b.d_bs1,d_ai1=b.d_ai1, rxdate1=b.rxdate1,bydate1=b.bydate1,byxx1=b.byxx2,d_an1=b.d_an1 from zzryxxb a join deleted b on a.gh = b.gh
set @gh11=(select gh from zzryxxb)
...返回多條記錄,系統無法賦值
for update as
if update(d_am2) and update(rxdate2) and update(bydate2)
declare @gh11 char(8),@d_am11 char(2),@d_bs11 char(2),@d_ai11 char(6),@rxdate11 datetime,@bydate11 datetime,@byxx21 char(28),@d_an11 char(2)
declare @d_am12 char(2),@rxdate12 datetime,@bydate12 datetime
set @gh11=(select gh from zzryxxb) --这一句不对--该为set @gh11=(select gh from deleted) 因为select gh from zzryxxb 返回的是多个值
set @d_am11=(select d_am2 from deleted where gh=@gh11)
set @d_bs11=(select d_bs2 from deleted where gh=@gh11)
set @d_ai11=(select d_ai2 from deleted where gh=@gh11)
set @rxdate11=(select rxdate2 from deleted where gh=@gh11)
set @bydate11=(select bydate2 from deleted where gh=@gh11)
set @byxx21=(select byxx2 from deleted where gh=@gh11)
set @d_an11=(select d_an2 from deleted where gh=@gh11)
set @d_am12=(select d_am2 from inserted where gh=@gh11)
set @rxdate12=(select rxdate2 from inserted where gh=@gh11)
set @bydate12=(select bydate2 from inserted where gh=@gh11)
if @d_am11<>@d_am12 and @rxdate11<>@rxdate12 and @bydate11<>@bydate12
begin
update zzryxxb set d_am1=@d_am11,d_bs1=@d_bs11,d_ai1=@d_ai11,
rxdate1=@rxdate11,bydate1=@bydate11,byxx1=@byxx21,d_an1=@d_an11 where gh=@gh11
end
GH值不是一条。。
所以建议游标
update a set d_am1=b.d_am1,d_bs1=b.d_bs1,d_ai1=b.d_ai1,
rxdate1=b.rxdate1,bydate1=b.bydate1,byxx1=b.byxx2,d_an1=b.d_an1
from zzryxxb a join deleted b on a.gh = b.gh