TSQL Tuesday #106: Triggers and the Spiral To Oblivion

davidfowler-42596, 2018-09-11


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.






Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis


1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren


1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren


360 reads