Asynchronous triggers using SQL Broker

SQL triggers exists to answer a very common need in applications to allow us to react when all sort of database events occur.
As you probably know, the basic triggers are insert ,update & delete triggers, that potentially allow us to react to every inserted/deleted/updated row.
One of the main problems with triggers starts when we attach to those events heavy processing.

One way to reduce the time the user waits for the related transaction to end is to change the behavior of those triggers to be asynchronous.

The main idea is to build an xml in the trigger and pass it to an sqlBroker queue for later processing, well..it doesn't have to be 'later', but it is async so it releases the main thread to continue & return to the client.

Let's look at the code, first we'll start with the declaration stuff:

--Enable broker

SET @strsql = 'ALTER DATABASE ' + db_name() + ' SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE '
EXEC sp_executesql @strsql

-- Create Message Type 
CREATE MESSAGE TYPE SampleMessage
AUTHORIZATION dbo 
VALIDATION = WELL_FORMED_XML; 

-- Create Contract 
CREATE CONTRACT SampleContract
AUTHORIZATION dbo
(SampleMessage SENT BY INITIATOR); 

-- Create Queue 
CREATE QUEUE dbo.SampleQueue WITH STATUS=ON, ACTIVATION 
(STATUS = ON, MAX_QUEUE_READERS = 1, 
PROCEDURE_NAME = spMessageProcSample,   EXECUTE AS OWNER); 


-- Create init Service 
CREATE SERVICE SampleServiceInitiator
AUTHORIZATION dbo
ON QUEUE dbo.SampleQueue (SampleContract); 

-- Create target Service 
CREATE SERVICE [SampleServiceTarget]
AUTHORIZATION dbo 
ON QUEUE dbo.SampleQueue (SampleContract); 

The trigger will use the SampleServiceInitiator to send an xml message (SampleMessage message type), this message will reach the SampleQueue queue which will be retrieved by SampleServiceTarget, this service will run the stored procedure named spMessageProcSample to handle the message.

Let's see a sample trigger code:

CREATE TRIGGER [trigSample] ON [dbo].SampleTable FOR UPDATE As 
SET NOCOUNT ON; 

DECLARE @MessageBody XML
DECLARE @TableId int

--get relevant information from inserted/deleted and convert to xml message
SET @MessageBody = (SELECT col1, col2 FROM inserted
FOR XML AUTO)


If (@MessageBody IS NOT NULL)
BEGIN

DECLARE @Handle UNIQUEIDENTIFIER; 
BEGIN DIALOG CONVERSATION @Handle 
FROM SERVICE [SampleServiceInitiator] 
TO SERVICE 'SampleServiceTarget' 
ON CONTRACT [SampleContract] 
WITH ENCRYPTION = OFF; 

SEND ON CONVERSATION @Handle 


MESSAGE TYPE [SampleMessageType](@MessageBody); 

END

Sample code of the reciever sp:

CREATE PROCEDURE [dbo].[spMessageProcSample] 
AS 
BEGIN 

DECLARE @message_type varchar(100) 
DECLARE @dialog uniqueidentifier, @message_body XML; 
WHILE (1 = 1) 
BEGIN -- Receive the next available message from the queue 
WAITFOR ( RECEIVE TOP(1) @message_type = message_type_name,     @message_body = CAST(message_body AS XML),     @dialog = conversation_handle
FROM dbo.SampleQueue ), TIMEOUT 500    if (@@ROWCOUNT = 0 OR @message_body IS NULL) 
BEGIN 
BREAK 
END 
ELSE 
BEGIN 
--process xml message here...

select ref.value('@SampleColumn[1]', 'int'),
from @message_body.nodes('//SampleMessage') T(ref)

END
END CONVERSATION @dialog 
END

END

That's it...some new objects to know and a bit weird syntax..but it does the work :-)

have fun

Diego