Hey you. Yes, you.
You. That used to create custom triggers in SQL Server.
Come closer…
![](https://duiliotacconi.com/wp-content/uploads/2024/06/454.gif?w=500)
First and foremost, we all need to respect cloud rules. And in da cloud no more access to backend to cheat with insert / update / delete / rename / whatever directly from SQL. No more Russian Roulettes under the hood. So, it is time to refactor using something else and decoupling with asynchronous processes (background tasks).
And if you REALLY can’t (really you can’t?) find out a suitable universal code compliant solution, then transform your triggers into stored procedure and invoke them at will (on-prem only, of course).
What I am talking about is a change introduced since Dynamics 365 Business Central 2023 Wave 2 (version 23.x) that is preventing from running custom triggers from underlying tables, elevating the following error:
The following SQL error was unexpected:
The target table 'Foo.dbo.MyTable$16d34n67-3cd7-17g2-ds5d-8hh12456k123' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.
Statement(s) could not be prepared.
Or very similar ones.
If you run a SQL Server profiler trace, you might spot out the OUTPUT clause in the TextData.
How do we know? We found this tattooed on our skins (or, better, on our servers – that is the same thing, for some weird people -) with old-fashioned triggers like
![](https://duiliotacconi.com/wp-content/uploads/2024/06/image-10.png)
(old-fashioned my a**, this was dated back March 2021. LOL.).
Why these changes? Microsoft responded swiftly
“It has never been supported to integrate directly to the SQL objects. … There was no direct intention to break direct SQL integrations. The schema change in version 23 was introduced to speed up performance.”
And act consequently by updating the following official documentation with a bold statement: Optimizing SQL Server Performance with Business Central – Business Central | Microsoft Learn
![](https://duiliotacconi.com/wp-content/uploads/2024/06/image-8.png?w=881)
No need to say anything extra. They had me at the “…speed up performance”. And we could also show up to customers, the official evidence. Chapeau Microsoft.
BUT
The picture of the SQL trigger shown few lines above was not the first time we fumble into this problem. Nope. No sir.
The very first kiss was an on-premises deployment that was using TableType ExternalSQL .
![](https://duiliotacconi.com/wp-content/uploads/2024/06/image-11.png?w=853)
See the baby below?
![](https://duiliotacconi.com/wp-content/uploads/2024/06/image-12.png)
Another server, another database, another planet. But same error thrown.
And also, the documentation stats that such tables are not Dynamics 365 Business Central businesses…
![](https://duiliotacconi.com/wp-content/uploads/2024/06/image-9.png?w=891)
If these are an outside world then why preventing such triggers to happen in external tables?
Well, I believe that external tables were not “considered” or “forgiven” when performing such metadata changes.
Personally, I am not interested in having this reverted back. I do prefer a consistent behaviour. And with a very simple documentation PR into the last Remarks section, we could make everyone aware about that.
![](https://duiliotacconi.com/wp-content/uploads/2024/06/tenor.gif?w=500)
Leave a comment