It’s the second Tuesday of the month which must mean only one thing…

We’re all skint and payday feels like a long way off???

Not quite, well maybe… but it’s also T SQL Tuesday!

This month’s invitation comes from Steve Jones and he’s asking us to write about triggers and our experiences of them.

So… let me tell you a story.

I would like to add now that I had nothing at all to do with the design of this system

Once upon a time there was a personnel database, that database held all sorts of information on a person, their name, their login details and the departments that they belonged to.

One day someone thought that it’d be a great idea, if whenever a new person was added into the system or when their department or job changed, an active directory account was automatically created for them or their permissions changed according to their job or department.

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?

Well we’re not quite into the realms of SkyNet yet (that was a different TSQL Tuesday post) so let’s have a think about what was going on here, although some of you are probably already way ahead of me here.

• A change is made to an account on Server 1
• An SSSB message is triggered detailing the change to Server 2
• The change is auctioned on Server 2
• The new trigger on Server 2 triggers an SSSB message to Server 1 detailing a change
• The change is reflected on Server 1
• The tigger on Server 1 fires off a message to Server 2, letting it know that a change has been made
• round and round we went

Every legitimate change would just kick off another endless loop of triggers and SSSB messages.

But Can’t SQL Server Spot This Sort of Thing?

Normally SQL Server can spot a recursive trigger and will kill it after 32 recursions.  So why wasn’t that happening here?

The issue was the use of Service Broker.  A Service Broker message isn’t within the scope of the transaction that sent it.  Because of that, our poor old SQL Server had no way of knowing that all these triggers that were firing off were related to each other and just ended up in a spiral to oblivion.