Asynchronous Triggers in SQL Server 2005/2008

sql sql-server sql-server-2005 triggers
Jose Basilio picture Jose Basilio · Apr 20, 2009 · Viewed 29.7k times · Source

I have triggers that manipulate and insert a lot of data into a Change tracking table for audit purposes on every insert, update and delete.

This trigger does its job very well, by using it we are able to log the desired oldvalues/newvalues as per the business requirements for every transaction.

However in some cases where the source table has a lot columns, it can take up to 30 seconds for the transaction to complete which is unacceptable.

Is there a way to make the trigger run asynchronously? Any examples.

Answer

Sean Reilly picture Sean Reilly · Apr 20, 2009

You can't make the trigger run asynchronously, but you could have the trigger synchronously send a message to a SQL Service Broker queue. The queue can then be processed asynchronously by a stored procedure.

Related questions

IF UPDATE() in SQL server trigger

If there's: IF UPDATE (col1) ...in the SQL server trigger on a table, does it return true only if col1 has been changed or been updated? I have a regular update query like UPDATE table-name SET col1 = 'x', col2 = 'y' …

Read More
Can I have two different insert triggers on the same table?

They seem to be allowed as I can see both my insert triggers listed under the table with different names. Is it common or a bad practice? I am using SQL Server 2005

Read More
Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More