Recursive Trigger Question

Question

Recursive Trigger Question

Jessica-240533

SSC Eights!

Points: 879
More actions
December 12, 2008 at 8:09 am

#202885

I am testing the following trigger:
ALTER TRIGGER [dbo].[trTitleDataReplicationDateTime_IU_TitleDataReceiveDateTime] ON [dbo].[TitleDataReplicationDateTime]
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
UPDATE TitleDataReplicationDateTime
SET TitleDataReceiveDateTime = GETDATE()
FROM TitleDataReplicationDateTime D
INNER JOIN Inserted I
ON D.TitleReferenceNumber = I.TitleReferenceNumber
INNER JOIN DB_Title T
ON I.TitleReferenceNumber = T.DTI_TITLE_REFRNC_NBR
WHERE
T.DTI_SHORT_LEGAL_DISPLY_TEXT LIKE '%;%'
AND T.DTI_SHORT_LEGAL_DISPLY_TEXT NOT LIKE '%;%;%'
AND T.DTI_TITLE_REFRNC_NBR NOT LIKE '%CS%'
AND D.TitleReferenceNumber like
CASE WHEN CHARINDEX(';',T.DTI_SHORT_LEGAL_DISPLY_TEXT) = 0 THEN Null
ELSE SUBSTRING(T.DTI_SHORT_LEGAL_DISPLY_TEXT, 1, CHARINDEX(';',T.DTI_SHORT_LEGAL_DISPLY_TEXT)- 1) + 'CS' + '%'
END
END
I get this error:
Msg 217, Level 16, State 1, Procedure trTitleDataReplicationDateTime_IU_TitleDataReceiveDateTime, Line 20
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
We do need this trigger to work recurisvely but obviously I don't have something in the trigger right. How can I determine what line it's stopping on?
Thanks!!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29422 More actions · Answer 1

We do need this trigger to work recurisvely but obviously I don't have something in the trigger right. How can I determine what line it's stopping on?

This is happening at the first line itself..

UPDATE TitleDataReplicationDateTime

SET TitleDataReceiveDateTime = GETDATE()

Whenever u're firing an update or insert query on table TitleDataReplicationDateTime, the trigger is fired; however, since the trigger itself is updating the same table, it'll again lead to firing of the same trigger and this nesting will continue until max limit of 32 is reached when server throws error.

I cant find exact solution that you need other than these two...

1. May be you need to set the recursive triggers database option to false.

2. Change your update/insert query to update the TitleDataReceiveDateTime column with getdate() function instead of updating it from within trigger..

Pradeep Singh

Jessica-240533 SSC Eights! Points: 879 More actions · Answer 2

Setting the recursive option to false stops the problem but the right updates are not occuring.

Here is some more information on what we are trying to do. When TitleDatareplicationDateTime is updated, we want to check DB_Title for every DTI_TITLE_REFRNC_NBR that matches inserted. From there, we want to grab the short legal description from the matching db_title record and grab everything in the short legal description prior to the ;. From there we want to use that portion of the short legal combined with a CS and a % to catch anything after the CS and find the matching record in titledatarecplicatoindatetime and update it's time with a getdate(). As you can see, that will cause the trigger to fire again. However, we added this line:

IF EXISTS (SELECT * FROM Inserted WHERE TitleReferenceNumber NOT LIKE '%CS') to the beginning of the trigger. That seems to stop the problem of it overloading when it finds a title number with a CS in DB_Title. However, it's still not functioning meaning that now an updated to the table on titlereferencenumber = '082229752003' does not update the record of titleeferencenumber = '0122871cs2' and that's what I need it to do.

Any Suggestions?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Jessica -

can you put together some test data? It's very difficult to follow what SHOULD be getting updated without seeing an example.

One of the uglier thoughts that MIGHT work is to put a check in that excludes anything where the TitleDataReceiveDateTime is within the last minute or so (so you can't pick up the same row again), but still - that's awfully kludgy.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

ps. One Orange Chip Points: 29422 More actions · Answer 4

refer this part of code

AND D.TitleReferenceNumber like
CASE WHEN CHARINDEX(';',T.DTI_SHORT_LEGAL_DISPLY_TEXT) = 0 THEN Null

u're comparing TitleReferenceNumber with NULL using LIKE

(When you run a query containing LIKE NULL as one of the OR operands, the query may return no results even though there are rows satisfying other OR predicates)

this is problematic in 2000 as reported by microsoft.

refer http://support.microsoft.com/kb/308370

you may try using empty string instead of NULL there...

Pradeep Singh

Navi's SSC Enthusiast Points: 167 More actions · Answer 5

Hi,

I am getting the same error..

"Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)."

Can you tell me is it comes due to trigger call.

In my case, I am also doing the same..

Updating the table and also created trigger for update and in trigger also update the same table.

Is this is the cause of ERROR. or any other thing i have to check...

[font="Arial Black"]Navi's:-)[/font]

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

Navi's (6/30/2010)
Hi,
I am getting the same error..
"Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)."
Can you tell me is it comes due to trigger call.
In my case, I am also doing the same..
Updating the table and also created trigger for update and in trigger also update the same table.
Is this is the cause of ERROR. or any other thing i have to check...

Probably. The real key here is why do you need recursive triggers? And, if you do absolutely need a form of recursion, why does it have to be a recursive trigger instead of a tight bit of code?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)