BethanyW
4Penny.net
Points: 7300

12/4/2012 2:59:18 PM

SQL - Keeping a trigger from firing twice

We recently upgraded a client from GP 9 to GP2010, and along with that the SQL Server upgraded from 2000 to 2008.

This client is heavily customized (because... that's that we do) and they have about 30 triggers running on Dynamics tables that power business logic. We were paniced to learn that SQL treads triggers differently in 2008. Triggers that didn't recurse now recuse to the maximum level allowed (32) and then throw an error. Very, very bad.

We experimented with turning off the SERVER setting of 'Allow triggers to fire others'... but we need that to happen.

We looked at the DATABASE setting of 'Recursive triggers enabled', that is set to false and we're OK with that. We don't want the same trigger calling itself.

We think it's largely the blame of new triggers that are present in GP 2010 that update the table that they're built on, and that fires our triggers... and you get an echo effect of triggers calling triggers.

So... here's our solution.

 

4Penny.net
Version: GP 2010
Section: SQL Scripts
Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
3