Multiple update triggers produce error
-
SQL Server 2000 is supposed to allow multiple UPDATE triggers, but when I try to put more than one update trigger on a table I get an error.
Here is a test script:
CREATE TABLE [TestNameTable] (
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NameID] [int] IDENTITY (1, 1) NOT NULL ,
[InsertTrigger1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UpdateTrigger1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InsertTrigger2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPdateTrigger2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_TestName1] PRIMARY KEY CLUSTERED
(
[NameID]
 
ON [PRIMARY]) ON [PRIMARY]
go
CREATE TRIGGER trupdater1
ON crm.dbo.TestNameTable
FOR update
AS
BEGIN
Declare @ID int
Declare @Name varchar(50)
set @ID = (Select NameID from inserted)
set @Name = (Select Name from inserted)
set @Name = @Name + 'Y'
Update TestNameTable set UpdateTrigger1 = @Name where NameID = @ID
end
-----------------------------------
go
CREATE TRIGGER trupdater2
ON crm.dbo.TestNameTable
FOR update
AS
BEGIN
Declare @ID integer
Declare @Name varchar(50)
set @ID = (Select NameID from inserted)
set @Name = (Select Name from inserted)
set @Name = @Name + 'Y'
Update TestNameTable set UpdateTrigger2 = @Name
where NameID = @ID
end
-----------------------------------------------------
I can then to two inserts as follows:
Insert into TestNameTable (Name) values ('Cat')
Insert into TestNameTable (Name) values ('Dog')
However, when I do an update, I get an error:
----------------------
Update TestNameTable set Name = 'Mouse' where NameID = 1
----------------
produces this error:
Server: Msg 217, Level 16, State 1, Procedure trupdater2, Line 16
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
I dont' understand why this isn't working
-
You either need to combine the logic into one single trigger, or use the IF UPDATE() logic within your triggers. What is happening here is that your triggers are recursively calling each other and hitting the nested trigger theshold of 32.
-
Thanks. I figured this out just after I posted.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply