sql2000触发器没有效率

[tiger998]

CREATE TRIGGER tr_mys_yz_update ON dbo.mys_yz 
FOR  UPDATE 
AS

if ( update( sfbz) )
begin

  --更新手术的收费标志
  Update a set sfbz = '1'  
  from ss_sqd a inner join inserted b on a.djh = b.sqdbh and b.sqdfl = 'ss' 
      inner join deleted  c on b.yzh = c.yzh and b.yzxh = c.yzxh and isnull( b.sfbz,'0' ) = '0' and isnull(c.sfbz,'0' ) = '1'

  --更新检验的收费标志
  Update a set sfbz = '1'  
  from jy_jyd  a inner join inserted b on a.jydh = b.sqdbh and b.sqdfl = 'jy' 
      inner join deleted  c on b.yzh = c.yzh and b.yzxh = c.yzxh and isnull( b.sfbz,'0' ) = '0' and isnull(c.sfbz,'0' ) = '1'
   
--更新检查的收费标志
Update a set sfbz = '1'  
  from jc_jcd  a inner join inserted b on a.djh = b.sqdbh and b.sqdfl = 'jc' 
      inner join deleted  c on b.yzh = c.yzh and b.yzxh = c.yzxh and isnull( b.sfbz,'0' ) = '0' and isnull(c.sfbz,'0' ) = '1'
 
end

以上的触发器，语法没有错误，但是update mys_yz.sfbz update后，触发器没有作用。。。。请高手门帮忙看看把。。。。谢谢

[dawugui]

你这什么逻辑啊?
三次更新同表同字段?

看不懂,帮顶.

[coleling]

没看懂你的红字部分的逻辑，为什么和蓝字部分不同？按我的理解应该是相同的...
Update a set sfbz = '1'
from ss_sqd a inner join inserted b on a.djh = b.sqdbh and b.sqdfl = 'ss'
inner join deleted c on b.yzh = c.yzh and b.yzxh = c.yzxh and isnull( b.sfbz,'0' ) = '0' and isnull(c.sfbz,'0' ) = '1'

To #1：
不是三次更新同表同字段，是别名都是a的三个不同的表

[SQL77]

如果没限制,递归都超过32了,汗

[--小F--]

当然有限制啊

[coleling]

and isnull( b.sfbz,'0' ) = '1' and isnull(c.sfbz,'0' ) = '0'

[SQLCenter]

[Quote=引用 5 楼 coleling 的回复:]

and isnull( b.sfbz,'0' ) = '1' and isnull(c.sfbz,'0' ) = '0'
[/Quote]

同意，估计是写反了。

不过，既然由0变1更新那一批表为1，难道由1变0那批表不需要改回0？不懂。

[SQLCenter]

最简单的就是那批表一律改为 inserted.sfbz

管它是由0变1还是由1变1。