SQL Server Performance Forum – Threads Archive

Problems With Trigger triggering another trigger

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. JPD

---

Is this a nested trigger ? If it is, you have to enable the ‘nested triggers’ option under Server properties. You can find this option under ‘Server Settings’ tab.

---

Yes. The nested trigger option is activated. I read the nested trigger chater in help of Sql Server, but still doesn’t work. Don’t know what else to try. Any other ideas? JPD

---

Would it be possible to rewrite this using a stored procedure instead of relying on triggers?
Alternatively, could table 1 use an INSTEAD OF trigger which generated the ‘exists’ field and then updates the base table?

---

In case you are using variables in your triggers, and they are used in your UPDATE statements, I would certainly debug the non-firing trigger. For testing, you can put a PRINT command in the trigger to write out the actual UPDATE statement with the variables resolved. Then test by doing an UPDATE on the first table from within the query analyzer: you’ll see the statement printed on the Messages tab. Don’t forget to remove the PRINT command completely from your trigger after debugging (do not just put — in front).

---

Hi Adriaan,
Just wondering why you say to remove the PRINT command completely and not just put — in front? Is there a reason other than making the code look tidy??
Thanks
Ben

---

I guess it’s just a precaution, but for instance you can sometimes run into problems with the word GO after — within a /* */ section in QA. In any case, you may well get a problem if a trigger with a PRINT statement fires when the trigger event is initiated from a client application other than QA.

---

Thanks Adriaan…I guess i will have to tidy my code up a bit more in future!

---

]]>