The purpose of events is to simplify business logic customization while not impeding upgradeability and general extensibility. However, there is one particular class of events that may cause troubles: OnAfter* table events. There are four of them: OnAfterInsert, OnAfterModify, OnAfterDelete, and OnAfterRename.
If you need them, you must be careful.
First of all, a common misconception about those events is that they execute inside the same write operation as code that we’d normally write inside the matching table triggers. For example, you might expect that the code that you put into OnAfterModify event is executing in the same operation as the code in the OnModify trigger inside the table.
If you read the documentation carefully, you’ll realize that it’s not the case: https://msdn.microsoft.com/en-us/library/mt299406(v=nav.90).aspx#DatabaseEvents
Here’s a simple code to prove that:
As the code here shows, the code executes on the same instance of Rec variable that OnInsert trigger uses, but it’s not in the same operation.
The point is – if you don’t persist your changes inside your event subscriber yourself, then those changes will be lost.
In the end, it translates to this: you cannot simply take your customizations out of an OnInsert trigger (or other table triggers) and put them into OnAfterInsert event subscriber (or matching ones) one on one.
In other words, this:
… does not translate to this:
In my opinion, it’s unfortunate that it does not. But this his how things are, no matter my personal opinion.
I do not want to argue (yet) whether this is a good behavior (or even if it was an intended behavior), but to give some suggestions around how to address this behavior properly.
Unfortunately, to persist our same-table changes from within OnAfter* triggers, we must explicitly persist them. You may not have an opinion about this Thus:
I said “you may not have an opinion about this” because there’s not much to argue here. If you want to persist changes, you must – well – persist them.
The problem with that Rec.MODIFY(FALSE) is multiple:
- We never had to do that in the “old” code
- We may not want to trigger modification from insertion trigger
- Modifications can cascade
Regarding point 1, this is a weak argument. “Old” and “New” ways do not necessarily have to map one-on-one.
Regarding point 2, it is something (for Microsoft) to consider. Why should we need to trigger explicit modification from insertion code? Why (unnecessarily) causing an extra database operation? Or, in case of multiple event subscribers (from multiple add-ins for example) causing multiple extra database operations?
And here we come to point 3. Not only it results in extra database operations, it can also cascade. Consider this:
So, you end in an endless loop, and eventually (in a couple of milliseconds) in a service tier crash. Which is on you, to make it absolutely clear!
Obviously, you must take care of this in your event subscribers, and it’s your responsibility to make sure endless trigger loops don’t happen. Yes, you never had to do it before, because you did make sure no triggers would execute by calling MODIFY with FALSE.
In the event-driven world, there is a similar way to make sure of this. Your table event subscribers come with a particular RunTrigger parameter, so take advantage of it:
A complaint that you may put here is “yes, fine, I can do it for my code, but how do I make sure 3rd party add-ins do the same”?
The answer is – you don’t. And you don’t have to.
An add-in that introduces the cascade will also suffer from the cascade, and whoever wrote it will have to resort to the IF NOT RunTrigger THEN EXIT pattern to solve their problem before it comes anywhere near becoming your problem.
Yes – I hear you – a cascade does not have to be direct, it can be circular. Like, modification of Vendor triggers modification of Item, and modification of Item triggers modification of Vendor.
Still, nothing to worry about. A single IF NOT RunTrigger THEN EXIT (yours) will take care of breaking the cascade
And yes – I hear you – it’s okay if you own the code, so you put this RunTrigger check; but there may be two separate add-ins that introduce circular cascade, and you don’t control the code. For example:
If these two come from two separate add-ins, then indeed, there is nothing you can do. If this ends up in your database, you’re toast.
However, it’s still not an excuse for you to not do the RunTrigger check in the code you write. You must do it, if for no other reason, then simply because that’s the real “translation” of the “old” in-place table trigger customization into the new event-driven architecture.
Think of it like this: in the old days it was as easy to cause circular database updates by calling MODIFY(TRUE) on any record from OnModify trigger of any table. And while you could make sure to call MODIFY(FALSE), you could not make sure that two separate add-ins did not accidentally do MODIFY(TRUE). So – this is not a new problem, it’s an old problem, just in different outfit.
The same way MODIFY(TRUE) was a bad practice in the old days, not checking for RunTrigger is a bad practice in these days. Remember it. And then apply it.
Yes, MODIFY(TRUE) did have it’s place. As much as there were valid reasons when you would certainly want to write MODIFY(TRUE) there are similar valid reasons when you’d not want to check for RunTrigger and simply unconditionally execute it. However, just as much as you had to be careful about not causing any unintended cascades every time you wrote MODIFY(TRUE) yesterday, you will have to be careful about it every time you intentionally decide to ignore the RunTrigger parameter tomorrow.
Now that I have explained the correct pattern, let me say this: I personally believe this is bad behavior. No matter how much we can get around it, it is still bad behavior. OnAfter table triggers are designed for customization purposes, so we could customize business logic while not changing standard code. An event that happens outside the write operation (while it certainly has its own place in the great scheme of things) is not a correct replacement for the matching table trigger that executes inside the write operation. And that’s all there is to it. It’s just not the same thing.
However, opinion is like a**hole. Everyone’s got one (and everyone thinks everyone else’s stinks).