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.