Asynchronous Triggers Using SQL Broker

Introduction

Service Broker is a new feature from SQL Server. Basically, it is an integrated part of the database engine. The Service Broker also supports an asynchronous programming model used in single instances as well as for distributed applications. It also supports queuing and reliable direct asynchronous messaging between SQL Server instances only.

In this article, we learn how to use Service Broker and triggers to capture data changes.

Service Broker in SQL Server

Service Broker is used to create conversations for exchanging messages between two ends, in other words, a source (initiator) and a target. Messages are used to transmit data and trigger processing when a message is received. The target and the initiator can be in the same database or different databases on the same instance of the Database Engine or in separate instances.

The Service Broker communicates with a protocol called "Dialog" that allows us bi-directional communication between two endpoints. The Dialog Protocol specifies the logical steps required for a reliable conversation and ensures that messages are received in the order they were sent.

How to create an asynchronous trigger?

Step 1

Enable the Service Broker on the database.

ALTER DATABASE [Database Name] SET ENABLE_BROKER

Sometimes the query above takes a long time to execute, the problem is that it requires special access to the database. Also, there might be a connection that is using this database with a shared lock on it; even if it is idle, it can block the ALTER DATABASE from completing. To fix the problem use ROLLBACK IMMEDIATE or a NO_WAIT statement at the termination options of ALTER DATABASE.

ALTER DATABASE [Database Name] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Step 2

Create an audit log table and create a procedure that helps to receive messages from a queue.

CREATE TABLE auditlog
(
            xmlstring xml
)
GO
CREATE PROCEDURE [dbo].[spMessageProcTest]
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.TestQueue ), TIMEOUT 500    if (@@ROWCOUNT = 0 OR @message_body IS NULL)
            BEGIN
                        BREAK
            END
            ELSE
                        BEGIN
                                    --process xml message here...
                                    INSERT INTO auditlog values(@message_body)
                        END
            END CONVERSATION @dialog
            END
END

Step 3

The next step is to create a Message Type.

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

-- Create Contract
CREATE CONTRACT TestContract
AUTHORIZATION dbo
(TestMessage SENT BY INITIATOR);

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

-- Create Service Initiator
CREATE SERVICE TestServiceInitiator
AUTHORIZATION dbo
ON QUEUE dbo.TestQueue (TestContract);
-- Create target Service
CREATE SERVICE [TestServiceTarget]
AUTHORIZATION dbo
ON QUEUE dbo.TestQueue (TestContract);

Step 4

Now we can test our logic.

To do that we need to create a table and write a trigger on it. In the trigger, we must send our message to the target. In this example, I am sending updated data as XML.

CREATE TABLE [dbo].[DepartmentMaster](
            [DepartmentId] [int] IDENTITY(1,1) NOT NULL,
            [Name] [varchar](50) NULL,
            [Description] [varchar](50) NULL,
 CONSTRAINT [PK_DepartmentMaster1] PRIMARY KEY CLUSTERED
(
            [DepartmentId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

--Insert some dummy values in tables
INSERT INTO DepartmentMaster VALUES ('Purchase','Purchase Department'),
 ('Sales','Sales Department'),
 ('Account','Account Department')

--Create trigger for update
CREATE  TRIGGER dbo.Trg_DepartmentMaster_Update
ON  dbo.DepartmentMaster
FOR UPDATE
AS
BEGIN
            SET NOCOUNT ON;
            DECLARE @MessageBody XML 
            DECLARE @TableId int 

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

            If (@MessageBody IS NOT NULL) 
            BEGIN 

                        DECLARE @Handle UNIQUEIDENTIFIER;  
                        BEGIN DIALOG CONVERSATION @Handle  
                        FROM SERVICE [TestServiceInitiator]  
                        TO SERVICE 'TestServiceTarget'  
                        ON CONTRACT [TestContract]  
                        WITH ENCRYPTION = OFF;  
                        SEND ON CONVERSATION @Handle  
                        MESSAGE TYPE [TestMessage](@MessageBody);
            END
END

When I update data the trigger will fire and the trigger creates a handle for conversion and sending data.

Current data of the DepartmentMaster table

Update Statement for the DepartmentMaster table

UPDATE DepartmentMaster SET Name = 'other Department' WHERE DepartmentId = 4

Audit Log table and value

<inserted DepartmentId="4" Name="other Department" Description="testdescription" />

Conclusion

The Service Broker is very useful for asynchronous integration, providing secure, scalable, and reliable messaging. With the help of a service Broker, we can create Asynchronous triggers.

Find more about Triggers in SQL Server here: Triggers in SQL Server