Recursive_Triggers Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Recursive_Triggers Problem

Author

Topic

azim
Starting Member

29 Posts

Posted - 2005-06-08 : 07:09:08

I Recieved an Error When I Set Recursive_Triggers On
And Try To Insert a New Record In Any Table Containing Trigger.

Error Message Is:
------------------
Maximum StoredProcedue, Function, Trigger or View nesting level exceeded (Limit 32).

Thanks

Kristen
Test

22859 Posts

Posted - 2005-06-08 : 07:39:21

Sounds like your triggers are going round in a loop (or you've got a complex scenario where a single update causes ripple-down update to a further 31 tables ...)

Kristen

azim
Starting Member

29 Posts

Posted - 2005-06-08 : 18:10:16

This Is The Trigger


CREATE TRIGGER tblPO_In_Det_Stamp ON dbo.tblPO_In_Det
FOR INSERT, UPDATE, DELETE 
AS
BEGIN

   UPDATE tblPO_In_Det

   SET    UserName = SYSTEM_USER,

         [Timestamp] = Current_timestamp

   FROM tblPO_In_Det 

        INNER JOIN Inserted

        ON (tblPO_In_Det.ID = Inserted.ID)

END

Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-08 : 18:47:21

Your trigger is causing itself to be fired recursively until the nesting level or 32 is exceeded.

Why do you need Recursive_Triggers On ?

CODO ERGO SUM

azim
Starting Member

29 Posts

Posted - 2005-06-09 : 14:48:50

I Have tblAccounts and tblJournal_Det
Each Accounts has a parent account
when i add new record in tblJournal_Det I want to insert the same valuse to parent account
so when i insert value for accountNo 1811
Trigger Directly Insert new record for accountNo 181
and using Recursive_Triggers to fire the trigger many times to
insert all records for parent accounts to update paren accoun balnces
AccountNo---AccountName-------Balance

1-----------Assets------------1000
18----------Lequid Money-----1000
181---------Banks------------1000
1811--------City Bank---------1000

Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-09 : 15:25:25

It would be bettter to do this logic in a stored procedure, and avoid using a trigger for this.

Developing a trigger to handle this kind of detailed application logic can quickly become a nightmare.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources