Asynchronous Triggers Using SQL Broker

ALTER DATABASE [Database Name] SET ENABLE_BROKER

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

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