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