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 112 Roundup

Shane O’Neill has a roundup of T-SQL Tuesday #112: I’m going to keep this intro short and sweet. I’d like to say it’s because I know these roundups are for acknowledging and thanking the people who have contributed, and this is the case! Mainly though it’s because I wrote this bit last and I’ve already […]

Read More

Enabling and Disabling Triggers

Kenneth Fisher shows how you can enable and disable triggers in SQL Server: I tend to feel that a lot of people who use triggers don’t really understand them. That said, every now and again you have to deal with them. And in particular (for this post) you might need to disable and then re-enable […]

Read More

Categories

September 2018
MTWTFSS
« Aug Oct »
 12
3456789
10111213141516
17181920212223
24252627282930