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.
SQL Undercover 
We had to code around something like this one time to prevent an update from firing multiple times. I don’t remember what we did, but we did find a way to keep it from triggering itself with some coding.
LikeLiked by 1 person
we only use service broker within the same server but we’ve run into similar issues. We resolved them by checking to see if the update/insert was actually updating a value (which is painful once you take NULL values into account)
LikeLiked by 1 person
The issue was NOT the use of Service Broker. I could easily create the same situation using triggers and linked servers. Yes, it is true that Service Broker activities run at the server level and not the database level so debugging is a more interesting activity. That does not entitle Service Broker to be blamed for a badly engineered and poorly tested solution.
LikeLike
Bryant, I wasn’t meaning to imply that Service Broker was an issue or that there’s anything wrong with using SSSB.
The issue was down to a poorly designed system, a lack of error checking and a case of too many cooks. I was merely giving an example of a time that I’d seen triggers go nasty.
Indeed, there are countless different ways that it’s possible to create an infinite loop of recusing triggers.
LikeLike