Using SQL Server Agent to Manage a Batch Process

We have an UPDATE trigger that occasionally triggers a lengthy batch process that's encapsulated in a stored procedure. The code works, but the process that issues the UPDATE statement can't continue until the trigger is complete. Can we return control to the process that issues the UPDATE statement without waiting for the trigger and its batch process to finish?

T-SQL is great for manipulating data, but for some purposes, it isn't as powerful as Visual Basic (VB), C++, and other languages. T-SQL doesn't provide direct support for asynchronous branching and execution, which is what you need. Calling your batch process from a trigger causes all your code to execute serially. In other words, the trigger calls the stored procedure, and the rest of the trigger code can't run until the stored procedure is finished running and returns control to the trigger. You can't change the serial nature of T-SQL execution, so you need to determine how to let multiple processes run independently. Here are a couple of options.

You can write an extended stored procedure to manage the batch process. To do so, you need a good understanding of C++, and the scripting is complicated. I don't recommend this approach, but I mention it for your information.

A simpler solution is to use SQL Server Agent (a standard feature of SQL Server) to execute the batch-processing code that the stored procedure contains. In the SQL Server Agent environment, simply create a job that calls the stored procedure you need. For example, you can use the following command to call a job from within your trigger:

EXEC msdb..sp_start_job 'waitfor15sec'

The job starts and runs under SQL Server Agent control. The sp_start_job procedure returns control to the calling T-SQL batch as soon as the job starts. Thus, the trigger waits only a short time while the SQL Server Agent job starts. The rest of the trigger code runs as soon as the job starts and execution control returns to the trigger. SQL Server Agent initiates a new SQL connection and uses it to run the batch procedure in the background. Of course, you need to design this process with blocking in mind because multiple processes might need to access the data simultaneously. The batch process that SQL Server Agent runs is an entirely new SQL connection, and this new connection could create blocks for the connection that fired it from the trigger.