Asynchronous Triggers in SQL Server 2005/2008

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.

Are triggers asynchronous (on asp.net command object for SQL Server 2008 R2 )

The trigger is run in the same context and the same transaction as the SQL operation that fired the trigger, e.g. in the same context and transaction as your UPDATE statement. The transaction (and thus the call from your ASP.NET code) doesn't terminate until the trigger has been executed. That's one of the main reasons why triggers should be used sparingly, and if used, the trigger must be very small, nimble and fast - do NOT do any heavy lifting and extensive processing and external calls in a trigger!

Asynchronous Triggers in Azure SQL Database

As far as I know, it's not possible to do directly in Azure SQL Database, but there are a few options:

As @gotqn mentioned in a comment, you can use Azure Automation/Runbooks; applied to Azure SQL specifically.

You can also check out database jobs.

Database Triggers, is it Async?

Almost certainly they would be synchronous since, otherwise, you've blown the whole point of atomicity away (a change may be made to a table atomically then the trigger may fail, meaning that your audit trail is useless).

I'm not saying async triggers aren't possible but I can't see them being of any use to you.

If you're worried about the table getting large, there are other ways to handle that. One is partitioning (based on date for example) or, if that's not available, periodic transfer of audit rows to date-based archive tables, followed by their deletion in the main table.

That way, the main table only ever holds data for the last N months and all other data is stored elsewhere.

Triggers are they asynchronous?

No, triggers are executed absolutely synchronously in the same way a stored procedure would be. This means that if you start doing things like updating counts in triggers, you can make a simple query executed with autocommit do a lot of work in a transaction hence be very likely to deadlock. You should be aware of this.

If you don't want it synchronous, I suppose you could make it do an insert into a table of pending changes, then have an asynchronous process which updates it. This would probably avoid a lot of deadlocks in the long run (inserting into a table with no secondary indexes cannot deadlock with another insert into the same table, AFAIK)

sql server 2005 trigger

create an update trigger like this in the example below , i have an emp table when its employee is updated to 'completed' that employee id is copied to table emp1

ALTER TRIGGER [dbo].[updtrigg] 
ON  [dbo].[emp] 
AFTER UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    declare @emp as varchar(50)
    declare @empid as varchar(5)
    SELECT @empid = empid, @emp = employee  FROM   inserted
     if(@emp = 'completed')
     begin
        INSERT into emp1 (employee) values(@empid)
     end
END

How triggers works internally in SQL Server

marc_s has given the correct answer. I will copy it for the sake of completeness.

TRIGGERS ARE SYNCHRONOUS

If you want to have a asynchronous functionality go for a SQL broker implimenation.

Triggers are triggered by events - and then they are executed - right now. Since you cannot control when and how often they are triggered, you should keep the processing in those triggers to an absolute minimum - I always try to make - at most - an entry into another table (an "Audit" table) or possibly put a "marker" row into a "command" table. But the actual processing of that info - running stored procedures etc. - should be left to an outside job - don't do extensive processing in a trigger! This will reliably KILL all your performance\responsiveness.

Asynchronous Triggers in SQL Server 2005/2008