>MS-SQL v7.x
>I have a slight problem with Trigger recursion. I have two tables that have to update/delete/insert each other to keep a name field in sync (don't ask, I just have to).
>
>In order to only update/delete/insert the one matching record, and keep the recursion to the one instance, I've created triggers on the two tables that use:
>
>IF ( (SELECT trigger_nestlevel( object_ID('trigger_name') ) ) > 1 )
> BEGIN
> RETURN
> END
>
>This works successfully for the DELETE and UPDATE triggers. The problem is with the INSERT trigger (same code snippet). Whichever table initiates the INSERT winds up with two inserts. I've attempted to ROLLBACK TRANSACTION in the IF, but that just rolls back everything.
>
>I know I am missing something (probably pretty rudimentary as well), but I am a a relative "newby" when it comes to T-SQL, so have pity on me and help me out someone!
>
>Thanks in advance,
>Phillip

Well I figured it out on my own, so someone let me know if this solution has it's own set of problems. By putting code like this in the first of both table's INSERT triggers:

IF ( (SELECT trigger_nestlevel( object_ID('tr_ThisTriggerName') ) ) > 1 )
BEGIN
RAISERROR( 'Ignore This Error', 0, -1 ) WITH SETERROR
-- This will return an ERROR 50000 to the calling trigger
RETURN
END

And putting code like this around the insert(s):

SAVE TRANSACTION xTestTran
INSERT INTO [dbo].[table] ( col1, col2, ... )
VALUES ( @val1, @val2, ...)
SET @lnError = @@ERROR
IF @lnError = 50000
BEGIN
ROLLBACK TRANSACTION xTestTran
END

Works like a charm! Both tables get one and only one INSERT. I come from a FoxPro background and there is no SAVE TRANSACTION (Transaction SavePoints)equivalent, so it took me awhile to figure this one out.

Phillip
"Were there is a will, there is a way"