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----更新前后内容相同便停止更新
end
