SQL Server trigger an asynchronous update from trigger?

Question

If a user inserts rows into a table, i would like SQL Server to perform some additional processing - but not in the context of the user's transaction.

e.g. The user gives read access to a folder:

UPDATE Folders SET ReadAccess = 1
WHERE FolderID = 7

As far as the user is concerned i want that to be the end of the atomic operation. In reality i have to now go find all child files and folders and give them ReadAccess.

EXECUTE SynchronizePermissions

This is a potentially lengthy operation (over 2s). i want this lengthy operation to happen "later". It can happen 0 seconds later, and before the carbon-unit has a chance to think about it the asynchronous update is done.

How can i run this required operation asychronously when it's required (i.e. triggered)?

The ideal would be:

CREATE TRIGGER dbo.Folders FOR INSERT, UPDATE, DELETE AS
   EXECUTEASYNCHRONOUS SynchronizePermissions

or

CREATE TRIGGER dbo.Folders FOR INSERT, UPDATE, DELETE AS
   EXECUTE SynchronizePermissions WITH(ASYNCHRONOUS)

---

Right now this happens as a trigger:

CREATE TRIGGER dbo.Folders FOR INSERT, UPDATE, DELETE AS
   EXECUTE SynchronizePermissions

and the user is forced to wait the 3 seconds every time they make a change to the Folders table.

i've thought about creating a Scheduled Task on the user, that runs every minute, and check for an PermissionsNeedSynchronizing flag:

CREATE TRIGGER dbo.Folders FOR INSERT, UPDATE, DELETE AS
   UPDATE SystemState SET PermissionsNeedsSynchronizing = 1

The scheduled task binary can check for this flag, run if the flag is on:

DECLARE @FlagValue int
SET @FlagValue = 0;

UPDATE SystemState SET @FlagValue = PermissionsNeedsSynchronizing+1
WHERE PermissionsNeedsSynchronizing = 1

IF @FlagValue = 2
BEGIN
   EXECUTE SynchronizePermissions

   UPDATE SystemState SET PermissionsNeedsSynchronizing = 0
   WHERE PermissionsNeedsSynchronizing = 2
END

The problem with a scheduled task is: - the fastest it can run is every 60 seconds - it's suffers from being a polling solution - it requires an executable

What i'd prefer is a way that SQL Server could trigger the scheduled task:

CREATE TRIGGER dbo.Folders FOR INSERT, UPDATE, DELETE AS
   EXECUTE SynchronizePermissionsAsychronous


CREATE PROCEDURE dbo.SynchronizePermissionsAsychronous AS

   EXECUTE sp_ms_StartWindowsScheduledTask @taskName="SynchronousPermissions"

The problem with this is: - there is no sp_ms_StartWinodowsScheduledTask system stored procedure

So i'm looking for ideas for better solutions.

---

Update: The previous example is a problem, that has has no good solution, for five years now. A problem from 3 years ago, that has no good solution is a table that i need to update a meta-data column after an insert/update. The metadata takes too long to calculate in online transaction processing, but i am ok with it appearing 3 or 5 seconds later:

CREATE TRIGGER dbo.UpdateFundsTransferValues FOR INSERT, UPDATE AS

UPDATE FundsTransfers
SET TotalOrderValue = (SELECT ....[snip]....),
    TotalDropValue = (SELECT ....,[snip]....)
WHERE FundsTransfers.FundsTransferID IN (
    SELECT i.FundsTransferID
    FROM INSERTED i
)

And the problem that i'm having today is a way to asychronously update some metadata after a row has been transitionally inserted or modified:

CREATE TRIGGER dbo.UpdateCDRValue FOR INSERT, UPDATE AS

UPDATE LCDs
SET CDRValue = (SELECT ....[snip]....)
WHERE LCDs.LCDGUID IN (
    SELECT i.LCDGUID
    FROM INSERTED i
)

---

Update 2: i've thought about creating a native, or managed, dll and using it as an extended stored procedure. The problem with that is:

• you can't script a binary
• i'm now allowed to do it

Answer 1

Use a queue table, and have a different background process pick things up off the queue and process them. The trigger itself is by definition a part of the user's transaction - this is precisely why they are often discouraged (or at least people are warned to not use expensive techniques inside triggers).

Answer 2

Create a SQL Agent job and run it with sp_start_job..it shouldn't wait for completion

However you need the proper permission to run jobs

Members of SQLAgentUserRole and SQLAgentReaderRole can only start jobs that they own. Members of SQLAgentOperatorRole can start all local jobs including those that are owned by other users. Members of sysadmin can start all local and multiserver jobs.

The problem with this approach is that if the job is already running it can't be started until it is finished

Otherwise go with the queue table that Aaron suggested, it is cleaner and better