Fire and Forget SQL Triggers?

I have a 3rd party app that uses SQL Server as a back end. Specifically we have it on SQL Server 2000, SQL Server 2008 and SQL Server 2008R2. Any solution must be available to all, but solutions for only some would still be of interest.

The app is bespoke, out of support and the vendor doesn't exist any more. What I want to do is when it writes an insert is have a trigger to read that insert and start off a complex process. However, the app doesn't return focus until the SQL insert completes which means a lengthy trigger will cause the app to think SQL Server isn't responding and cancel/rollback the transaction.

Is there any kind of trigger that will 'release' immediately and then continue on with its processing? Or perhaps some other solution that I can implement internal to SQL Server.

I think I would build a table with a queue of some sorts, and have a job poll that table and pick up any work it has to perform.

So for example your trigger writes a record id in a table called processqueue, then you have a job that picks up all the records from the processqueue table and do it's heavy lifting for each and every record, removing the record from the queue once finished.

Schedule that job to run according to latency requirements

StackExchange Database Administrators Q#108429, answer score: 6