Delayed or Asynchronous DML Trigger Execution

Question

I have created a DML trigger (AFTER INSERT, UPDATE, DELETE) on a table
Trigger's logic takes about 30 seconds to execute

So if you change even 1 row, it takes ~ 30 seconds due to trigger execution
Developer asked me "Is there a chance that the trigger could be a fire & forget action?"

I said no, but is it really so ?

Question:

Can trigger be executed in "asynchronous" mode?
Application updates couple of rows in few ms, and thinks that transaction is completed, and then trigger is silently executed under the hood ?

I understand that this actually does not look good from consistency point of view, but still, is it possible ?

• Zane: The trigger logic needs to be changed. Single row change causing a 30 second trigger execution is going to be a pretty huge bummer in your Azure environment.
• Aleksey Vitsko: @Zane I would not say so. Table is changed rarely, and btw it does not matter 1 row or 8 million rows change. It will be +30 sec due to trigger in each case, and the time is not critical to Application at all
• NoWar: mssqltips.com/sqlservertip/6380/…

Answer 1

As far as I'm aware this is not possible with the triggers feature of Azure / SQL Server. One reason likely being, as you mentioned, it breaks the Consistency property of the ACID principals of a database:

...any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof. This prevents database corruption by an illegal transaction...

Triggers are meant to fire transactionally in lockstep with the operation that caused them to fire. Imagine if the row of data that the trigger was dependent on was deleted before the trigger got to execute asynchronously. This could get you into a lot of trouble.

Alternatively, you can have a trigger that simply adds a row to a queue table, and a SQL Agent Job for example, that constantly runs checking that queue table and firing a stored procedure with the logic from your original trigger to emulate an asynchronous event. Perhaps that would improve your workflow?

• Aleksey Vitsko: thanks for response J.D.! I edited my question, it is Azure SQL DB, not SQL Server 2017. Added later by mistake
• J.D.: @AlekseyVitsko Gotcha, no prob! Likely the answer is almost exactly applicable just as well to Azure SQL DBA too.
• Aleksey Vitsko: going to mark as answer, waiting to get 1 vote up on the Question. Hope to get Socratic badge at least by 2030 :D
• J.D.: @AlekseyVitsko May the badge be yours lol. :)