Trigger Spirals

David Fowler tells a story of woe, one which is totally not his fault:

To do this, a trigger was created which would send all the details via a Service Broker message to another SQL Server, this SQL Server was used to hold details of the AD accounts and from there, changes were automatically propagated out to AD.

This was working well until one day when it was realised that any changes to account permissions in AD weren’t reflected in the personnel database.  To solve this, another trigger was created to send a Service Broker message back to the personnel database with details of the change.

This was where I came in, it was noticed that the system had started to run slower and slower, not only that but permissions seemed to be constantly changing for no obvious reason.  Were the machines finally waking up and taking over?

There’s a reasonable explanation here, for some definition of reasonable.

Related Posts

T-SQL Tuesday Trigger Roundup

Steve Jones has a bunch of links about triggers this month: This month was my turn to host T-SQL Tuesday. I chose Trigger Headaches or Happinessas the topic, and I am glad that there have been quite a few responses. I started the review almost immediately,and here are a few highlights. I separated these based on […]

Read More

Triggers: Good, Bad, Mostly Ugly

Bob Pusateri walks us through a poorly-written DDL trigger: First, the scope. While the application that deployed this trigger has its own database, AppDB, this trigger is firing for events on the entire server, which is what the ON ALL SERVER line means. Any qualifying event on this server, even if it pertains to another application with a separate […]

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Categories

September 2018
MTWTFSS
« Aug  
 12
3456789
10111213141516
17181920212223
24252627282930