Hello Mitch, a late and not the best answer (because we don't have complicated databases), but hopefully better than no answer.

>I am having a real problem understanding & maintaining the interacting triggers and stored procedures in my
>DB. Because of poor coding I've got some serious performance problems which I need to fix but now I have
>accumulated so many nested things going on that I'm terrified to change anything in case it breaks or corrupts
>the business logic.

>My code & logic is such a tangle that I can't understand it (& I wrote it!!). Often the problems are of the type
>where Table A has an update trigger that updates Table B, which has an update trigger which updates table
>A etc. A nightmare to debug.

17 years ago I worked at a place where we had a complex data model. There we had rules about which order tables should be updated. Tables which have triggers that update each other at least sounds messy in my ears, have you considered e.g. adding a Table C that triggers on both A and B can update rather than A and B updating each other?

>Is there a set of best practices for dealing with this problem by structuring triggers and sp's in an optimal way?
>For example is it better to:

>*minimise the number of triggers by maximising the qty of code in each?
>*maximise the number of triggers by minimising the qty of code in each?
>*Use triggers mainly to call stored procedures?

I don't know. Intuitively I'd say that each trigger should be easily understandable and that beyond that it doesn't matter whether you put your code in a few or a lot of triggers. It certainly cannot replace good design.

>Are there best practices for trigger & SP naming conventions and trigger order etc?

Beyond being consistent in your naming conventions and avoiding having the same order for several triggers, I don't know.

Set