Trigger indirect recursion problem 2

[SQLSister]

Ok, I'm in trigger hell and my brain has completely shut down.

The situation: we are in the middle of replacing one web site with a rebranded one. The new site, in addition to having a new name and new look, is improving some things about the way we were currenlty doing business. Both sites will be operating simulatneously for about a month. My problem is that my new boss changed how we store customer information and thus we created new tables which are not exact matches for the old tables. We have to leave the old ones in place or the old site will break. We also have a requirement that a customer should be able to log in to either site if he registers on one and he should, of course, be able to see all the transactions done on either site or make changes to his customer profile which would reflect on either site.

Now I have to write triggers on a group of tables that have customer data. So an insert or delete or update on the tables that store the old sites's data will trigger and insert, update, or delete on the new tables and vice versa. I'm sure you can already see where this is a problem. So I get into a recursive situation and immediately get an error message saying I've hit the limit on the number of nested triggers I can hit. Now I figured out that if I set a field with a flag on it that tells me if the record came from a trigger or a regular insert ro update, that it would stop recursing and work fine. This is fine for the inserts and the initial update and the delete. However, what happens the second time the customer wants to update the record? It is already flagged as being changed by a trigger from the first update so now the trigger won't fire at all for the second real update. So what do I do to get the data back into a state where it the trigger can fire again?

Does any of this makes sense or am I babbling incoherently?

 

[TheBugSlayer]

Wow, this is a challenging one!

I am thinking that you could have a third table that would hold all the changes made to the records from either site, with a field that indicates which site the change is coming from. You will also have a field that is a flag indicating whether the change has been applied to the target table. You will then have a job or something else monitoring the table for changes and applying them, just like the Log Reader in a Transactional Replication.

Now let me know if I have further lost you...

 

[LogicalmanUS]

DQLSister,

Not only do you answer the hard questions, you have produce harder one's further!! Hmm.

May I suggest a simply workaround, as it is just for a month whilst the DBs run in tandem, turn off the triggers and use SProcs to do the dirty work, in this way you can update both tables at the same time (INSERT, UPDATE or DELETE), and not fall into the recursion problems.

This may not be the best answer, but it is a workaround in the short term. In the meantime, I'll just burn more brain juice trying to work out how avoid the recursion...

Logicalman

 

[danwhite]

Maybe you should look at my trigger I posted on my feed, it may help you.

 

[LogicalmanUS]

SQLSister,

Here's a thought.

What is the primary key for the record? If it does not include any identity field, then you can check whether there is already a record in the other table matching the values (for Insert or Update) or whether there is no record matching the values (for Delete) and, depending upon the result take no action as appropriate. This should break the endless loop scenario.

Logicalman

 

[dky1e]

Use TRIGGER_NESTLEVEL

Example:

create table tmp1
( i int)

create table tmp2
( i int)

GO
create trigger t_tmp1 on tmp1
for insert
as
if( TRIGGER_NESTLEVEL() = 1 )
insert into tmp2(i)
select i from inserted
GO
create trigger t_tmp2 on tmp2
for insert
as
if( TRIGGER_NESTLEVEL() = 1 )
insert into tmp1(i)
select * from inserted
GO

insert into tmp1(i) values(1)
select *
from tmp1
select *
from tmp2

insert into tmp2(i) values(2)
select *
from tmp1
select *
from tmp2

GO
drop table tmp1
drop table tmp2

 

[LogicalmanUS]

dkyle1,

Interesting solution. I have one question though, the NESTED_LEVEL function, does it refer to number of triggers started from ONE transaction? I understand that not passing the objectID causes the total number of triggers kicked off to be returned, but from which point?

From what SQLSister indicated, due to the action of Trigger1, a row is Inserted/Update/deleted from Table2, which then kicks off Trigger2. Would this cause the nesting?

Does this mean Trigger1 and Trigger2 are related? Or just by the stack?

This looks good though and I would like to try it in one of my own applications, if I may.

Logicalman

 

[zhavic]

Logicalman,

From what SQLSister indicated, due to the action of Trigger1, a row is Inserted/Update/deleted from Table2, which then kicks off Trigger2. Would this cause the nesting?

Yes, that is it. Look at this:
1. Due to the action of Trigger1, a row is Inserted/Update/deleted from Table2, which then kicks off Trigger2.
2. Due to the action of Trigger2, a row is Inserted/Update/deleted from Table1, which then kicks off Trigger1.
3. Go to the point (1)

Does this mean Trigger1 and Trigger2 are related? Or just by the stack?
When the triger is fired, it is similar to the procedure or function call. SQL server stores 'return possition' into stack, and then run trigger. When last statement of the trigger was executed, it pop up 'return possiton' from stack and continue executing from that point. But stack size is now only 32 'return possitions' for ONE session / connection.

TRIGGER_NESTLEVEL() function return total number of triggers kicked off for ONE session / connection.

Solution from 'dkyle1' looks very good. ( a star for you 'dkyle1' )
And little suggestion to that solution
use
TRIGGER_NESTLEVEL( object_ID( 't_tmp2' ) ) = 0 in trigger t_tmp1, and
TRIGGER_NESTLEVEL( object_ID( 't_tmp1' ) ) = 0 in trigger t_tmp2

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[LogicalmanUS]

zhavic,

Thanks for putting me straight on that, my head was beginning to buzz (very loudly!!)

Logicalman