SQL触发器问题,急~~~~~可行的马上结帐
各位,小弟写了个触发器.但在更新时出现以下问题:[Microsoft]]ODBC   SQL   Server   Driver][SQL   Server]超出了存储过程,函数,触发器或试图的最大嵌套层数(最大层数为32)

CREATE   TRIGGER   [Tri_TFWupdate]   ON   [dbo].[Tfangwuinfo]  
FOR   UPDATE
AS
begin
declare   @shouchu   bit   select   @shouchu=Tfangwuinfo.shouchu   from   Tfangwuinfo
if   update   (shouchu)  
if   (@shouchu=0)
insert   Tyezhuinfo(yezhuID,yezhuname)   select   fangwuID,chanquanren   from   inserted   i
if   update   (chanquanren)
update   Tyezhuinfo   set   yezhuname=i.chanquanren   from   inserted   i   where   Tyezhuinfo.yezhuID=i.fangwuID
end

看明白的朋友就看,不明白的只要告诉我如何在if   update(shouchu)中再嵌套一个if的判断语句就行了.

------解决方案--------------------
CREATE TRIGGER [Tri_TFWupdate] ON [dbo].[Tfangwuinfo]
FOR UPDATE
AS
begin
declare @shouchu bit
select @shouchu=shouchu from Tfangwuinfo

if (update (shouchu) and @shouchu=0) --- & 换成 and
insert Tyezhuinfo(yezhuID,yezhuname) select fangwuID,chanquanren from inserted i

if update (chanquanren)
update Tyezhuinfo set yezhuname=i.chanquanren from inserted i where Tyezhuinfo.yezhuID=i.fangwuID
end

------解决方案--------------------
CREATE TRIGGER [Tri_TFWupdate] ON [dbo].[Tfangwuinfo]
FOR UPDATE
AS
begin
/*
declare @shouchu bit
select top 1 @shouchu=Tfangwuinfo.shouchu from Tfangwuinfo
*/
--这里是不是想如果shouchu=0才更新,可为什么是从Tfangwuinfo中取呢?
if (update(shouchu) and exists(select 1 from inserted i,deleted d where i.fangwuID=d.fangwuID and isnull(i.shouchu,0) <> isnull(d.shouchu,0) and i.shouchu=0))
insert Tyezhuinfo(yezhuID,yezhuname)
select i.fangwuID,i.chanquanren
from inserted i,deleted d
where i.fangwuID=d.fangwuID and isnull(i.shouchu,0) <> isnull(d.shouchu,0) and i.shouchu=0
if update (chanquanren) and exists(select 1 from inserted i,deleted d where i.fangwuID=d.fangwuID and isnull(i.chanquanren, ' ') <> isnull(d.chanquanren, ' '))
update Tyezhuinfo
set yezhuname=i.chanquanren
from inserted i,deleted d
where Tyezhuinfo.yezhuID=i.fangwuID
and i.fangwuID=d.fangwuID and isnull(i.chanquanren, ' ') <> isnull(d.chanquanren, ' ')
and Tyezhuinfo.yezhuname <> i.chanquanren
end
------解决方案--------------------
--禁止递归就行了.请在查询分析器运行下面语句禁止递归--
EXEC sp_dboption '数据库名 ', 'recursive triggers ', 'OFF '

exec sp_configure 'nested triggers ',0
reconfigure

------解决方案--------------------
当然也可以在触发器里自己加条件防止递归超过32层

CREATE TRIGGER [Tri_TFWupdate] ON [dbo].[Tfangwuinfo]
FOR UPDATE
AS
begin
declare @shouchu bit select @shouchu=Tfangwuinfo.shouchu from Tfangwuinfo
if (update(shouchu) and @shouchu=0)
insert Tyezhuinfo(yezhuID,yezhuname) select fangwuID,chanquanren from inserted i
if update (chanquanren)
update Tyezhuinfo set yezhuname=i.chanquanren from inserted i where Tyezhuinfo.yezhuID=i.fangwuID and yezhuname <> i.chanquanren--更新前后内容相同便停止更新
end

CREATE TRIGGER [Tri_update_inYZ] ON [dbo].[Tyezhuinfo]
FOR UPDATE
AS
begin
if update (yezhuname)
update Tfangwuinfo set chanquanren=i.yezhuname from inserted i where Tfangwuinfo.fangwuID=i.yezhuID and chanquanren <> i.chanquanren----更新前后内容相同便停止更新