Problems With Trigger triggering another trigger

conde_almasy

Hello.

I have a problem with my sql server 2000 database regarding the use of triggers.

I use two triggers that effect three diferent tables.

The First trigger is an "after insert" on table1, that trigger checks IF the same item

inserted in table1 exists in table2, if is true, it will update a column of that item in

table2.

The second trigger executes "after an UPDATE" in the column mentioned of table2.

So this means that trigger1 ends up triggering trigger2.

That's where the problem comes, trigger2 will eventually end up updating a

column in table3, but that update never happens, although the trigger executes correctly and

every line is called ( I used an Raiserror to check after every line), but the actually UPDATE never occurs.

trigger2 works if I triggered it by updating the columns on table2 manually, but

when that trigger2 is triggered by an update sentence called in trigger1, it doesnt work.

I need to let you know, I am NOT updating the same columns on the same tables.

I don't know that is going on, it seems that when a trigger is executing and inserts or

updates a column in a table that triggeres a second one, that second trigger cannot execute

another transaction of the same kind, it's like there was a RollBack Transaction at the end of trigger2.

Do you know what is going on? I assure you, both trigger work with no problem separatly.

Thank you in advanced.

Sorry if I was too confusing.

2lazybutsmart

Nested triggers give many people nonstop headaches. I had a similar problem to yours because I had implicit transactions on and I kept viewing the results in another query window which never displayed the updated records...

anyways, mind if you post your two triggers and perhaps we can beat them triggers again.

2lbs.