Ghazi Sarhan's Blog - Asynchronous Trigger

Sunday, December 16, 2007

I developed a small application for my office, in this application I used a trigger to read data from a web service, then update the table after the record is inserted.

Everything was working fine, except... the web service sometimes takes time to come with the result, what keeps the user waits for a long time before he gets a confirmation that the record was saved.

I thought about an asynchronous trigger, I did some google, and found that asynchronous triggers can be done with Sql Server's Service Broker, little bit from here and there, I built the following as a template for my asynchronous triggers:

USE master

-- Check if database exists and drop it

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

DROP DATABASE AsyncTrigger

-- Create the database

CREATE DATABASE AsyncTrigger

-- Enable Service Broker

ALTER DATABASE AsyncTrigger

SET ENABLE_BROKER

USE AsyncTrigger

-- Create our test table

CREATE TABLE Table_1(

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

[Col1] [int] NOT NULL,

[Col2] [int] NOT NULL,

[Sum] [int] NULL)

-- This procedure will run asynchronously and execute our task

CREATE PROCEDURE usp_Processor

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

-- Create Message Type

CREATE MESSAGE TYPE [MyMessage] VALIDATION = WELL_FORMED_XML;

-- Create Contract

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

-- Create Queue

CREATE QUEUE [MyQ] WITH ACTIVATION

(STATUS = ON, MAX_QUEUE_READERS = 1,

PROCEDURE_NAME = usp_Processor,

EXECUTE AS OWNER);

-- Create Service

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

-- This trigger will send our message to the queue

CREATE TRIGGER Table_1_Insert ON Table_1 AFTER INSERT

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

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

Sunday, December 16, 2007 5:01:14 PM UTC

Comments [2] -
SQL Server

Wednesday, July 01, 2009 3:28:37 AM UTC

hm.. asynchronous triggers is not actually triggers, but queueing triggers..?.. helpful example. thanks.

tsohr

Sunday, August 30, 2009 8:01:52 AM UTC

Best quality example, Thank You!

Name
E-mail
Home page

	Remember Me
Comment (Some html is allowed: `a@href@title, strike`) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.
Enter the code shown (prevents robots):
Live Comment Preview