sql2000触发器没有效率

tiger998 2010-10-18 10:47:06
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后,触发器没有作用。。。。请高手门帮忙看看把。。。。谢谢
...全文
69 点赞 收藏 7
写回复
7 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
SQLCenter 2010-10-19
最简单的就是那批表一律改为 inserted.sfbz

管它是由0变1还是由1变1。
回复
SQLCenter 2010-10-19
[Quote=引用 5 楼 coleling 的回复:]

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

同意,估计是写反了。

不过,既然由0变1更新那一批表为1,难道由1变0那批表不需要改回0?不懂。
回复
coleling 2010-10-19
and isnull( b.sfbz,'0' ) = '1' and isnull(c.sfbz,'0' ) = '0'
回复
--小F-- 2010-10-19
当然有限制啊
回复
SQL77 2010-10-19
如果没限制,递归都超过32了,汗
回复
coleling 2010-10-19
没看懂你的红字部分的逻辑,为什么和蓝字部分不同?按我的理解应该是相同的...
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的三个不同的表
回复
dawugui 2010-10-18
你这什么逻辑啊?
三次更新同表同字段?

看不懂,帮顶.
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-18 10:47
社区公告
暂无公告