Recursive Trigger - 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 2005 Forums

Transact-SQL (2005)

Recursive Trigger

Author

Topic

Jessica
Starting Member

23 Posts

Posted - 2008-12-12 : 10:06:54

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!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-12 : 10:24:14

i think the update is causing trigger to fire again recursively. can i ask what your exact requirement is?

Jessica
Starting Member

23 Posts

Posted - 2008-12-12 : 10:49:37

Sure.

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.

Does that help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-13 : 00:45:23

so you want it to continue recvursively. then what will be terminating condition?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-13 : 03:27:23

You can use @@NESTLEVEL.

"
AS

IF @@NESTLEVEL > 10 RETURN

...
"

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources