USE master

GO

IF EXISTS(SELECT * FROM sys.databases where name = 'AsyncTrigger')

DROP DATABASE AsyncTrigger

GO

CREATE DATABASE AsyncTrigger

GO

ALTER DATABASE AsyncTrigger

SET ENABLE_BROKER

GO

USE AsyncTrigger

GO

CREATE TABLE Table_1(

[id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,

[Col1] [int] NOT NULL,

[Col2] [int] NOT NULL,

[Sum] [int] NULL)

GO

CREATE PROCEDURE usp_Processor

AS

BEGIN

SET NOCOUNT ON;

DECLARE @Handle UNIQUEIDENTIFIER;

DECLARE @MessageType SYSNAME;

DECLARE @Message XML;

RECEIVE TOP (1)

@Handle = conversation_handle,

@MessageType = message_type_name,

@Message = message_body

FROM [MyQ];

IF(@Handle IS NOT NULL AND @Message IS NOT NULL)

BEGIN

DECLARE @tmpT Table(id int, Col1 int, Col2 int, [Sum] int);

insert into @tmpT SELECT

Table1.Column1.value('@ID', 'INT') as id,

Table1.Column1.value('@Col1', 'INT')as col1,

Table1.Column1.value('@Col2', 'INT') as col2,

Table1.Column1.value('@Sum', 'INT') as [sum]

FROM @Message.nodes('/row') AS Table1(Column1)

Update Table_1 set [Sum] = tmpT.Col1 + tmpT.Col2

from @tmpT as tmpT Inner Join Table_1 as T

on tmpT.id = T.id;

END -- End If

END -- End Procedure usp_Processor

GO

CREATE MESSAGE TYPE [MyMessage] VALIDATION = WELL_FORMED_XML;

GO

CREATE CONTRACT [MyContract] ([MyMessage] SENT BY ANY);

GO

CREATE QUEUE [MyQ] WITH ACTIVATION

(STATUS = ON, MAX_QUEUE_READERS = 1,

PROCEDURE_NAME = usp_Processor,

EXECUTE AS OWNER);

GO

CREATE SERVICE [MyService] ON QUEUE [MyQ] ([MyContract]);

GO

CREATE TRIGGER Table_1_Insert ON Table_1 AFTER INSERT

AS

BEGIN

SET NOCOUNT ON;

DECLARE @MessageBody XML;

set @MessageBody = (SELECT ID, Col1, Col2, [Sum] FROM Inserted FOR XML RAW, TYPE);

DECLARE @Handle UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @Handle

FROM SERVICE [MyService]

TO SERVICE 'MyService'

ON CONTRACT [MyContract]

WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @Handle

MESSAGE TYPE [MyMessage](@MessageBody);

END

GO

Now execute the following:

Insert Into Table_1 (Col1, Col2) Values (34, 78)

Select * From Table_1

Waitfor Delay '00:00:03'

Select * From Table_1

The result will be:

id             Col1        Col2        Sum
----------- ----------- ----------- -----------
1              34           78           NULL

id             Col1        Col2        Sum
----------- ----------- ----------- -----------
1              34           78           112