触发器中if子句的问题

[hljhl]

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 @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
以上语句的意思是只有当同时修改ZZRYXXB中的d_am2,rxdate2,bydate2字段时，触发器才动作。但现在对zzryxxb的任何操作都有如下提示：“服务器: 消息 512，级别 16，状态 1，过程 tri_xl_zzryxxb，行 6
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。
语句已终止。”
请指教！

[txlicenhe]

如下语句返回值不只一条：
select gh from zzryxxb
select d_am2 from deleted where gh=@gh11)

1：如果你一条条修改或删除时应该不会出现如上错误。
2：可以改成游标。

[magnetmoon]

...
set @gh11=(select gh from zzryxxb)
...

返回多條記錄，系統無法賦值

[sdhdy]

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

[friendliu]

set @gh11=(select gh from zzryxxb)
GH值不是一条。。
所以建议游标

[txlicenhe]

感觉好象可以只要一句就搞定，没必要用游标和变量。

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