Service Broker: add message to queue

In this post let’s dwell on adding messages to queues. In our system we prefer doing it inside triggers. Why triggers? There are several reasons:

  • You cannot miss trigger event, whenever you perform update through web-service, or direct in the database.
  • Trigger is always inside your transaction.
  • Also you can get data from the record state before update. This can be very useful.

On the other hand, triggers are not really good way to store any logic, but if it contains only adding messages to queue, this may be rather good option. In any way, we’ve decided to use triggers.

I will describe AFTER UPDATE trigger, as AFTER INSERT will be the same, but simpler as only one record will be proceeded through AFTER INSERT trigger.

-- Head of the script will make it executable whether it exists in the database or not.
SET NOEXEC OFF                              
GO
IF OBJECT_ID('<TriggerName>') IS NOT NULL
BEGIN
       SET NOEXEC ON
END   
GO
-- Creating empty object
CREATE TRIGGER dbo.<TriggerName>
on dbo.<TableName>
AFTER UPDATE
AS
RAISERROR ('Object is empty. Error!', -- Message text.
               16, -- Severity.
               1 -- State.
               );
GO
SET NOEXEC OFF
GO
ALTER TRIGGER dbo.<TriggerName>
ON dbo.<TableName>
after  UPDATE
AS
       DECLARE @conversationHandle UNIQUEIDENTIFIER
 
-- Declare cursor for adding messages
-- as while multi row update
-- table inserted will have data for all updated records,
-- and trigger will be executed at the end of batch update
-- not after update of each record
 
       DECLARE cur CURSOR
       READ_ONLY LOCAL FORWARD_ONLY
       FOR SELECT
             inserted.id,
-- some additional logic
             case
                    when (inserted.IsDMark > deleted.IsDMark) then 3
                    when (inserted.IsDMark < deleted.IsDMark) then 4
                    when (inserted.IsDMark = deleted.IsDMark) then 1
             end as actionid
       FROM inserted
       left join deleted on inserted.id = deleted.id
       -- some conditions
       where
       (inserted.IsPosted > deleted.IsPosted)
       and inserted.IsReturned=0
 
    declare @documentID int
    declare @actionid int
    declare @xml xml
 
 
       OPEN cur
 
       FETCH NEXT FROM cur INTO @documentID, @actionid
       WHILE (@@fetch_status = 0)
       BEGIN
       -- open the dialog
             BEGIN DIALOG CONVERSATION @conversationHandle
                    FROM SERVICE [//TMS/ServiceBroker/WarehouseManagementProcessing/Init]
                    TO SERVICE '//TMS/ServiceBroker/WarehouseManagementProcessing/Target'
                    ON CONTRACT [//TMS/ServiceBroker/WarehouseManagementProcessing]
                    WITH ENCRYPTION = OFF;
 
       -- create xml for Service Broker message
                    set @xml = (select
                                  @documentID as 'ObjectID'
                             , @actionID as 'ActionID'
                             , getdate() as 'VerDateTime'
                    for xml path(''), type, root ('root'));
 
       -- send message
                    SEND ON CONVERSATION @conversationHandle
                           MESSAGE TYPE [//TMS/ServiceBroker/WarehouseManagementProcessing/StandardMessage]
                           (@XML)
 
       -- close dialog
             END CONVERSATION @conversationHandle
 
             FETCH NEXT FROM cur INTO @documentID, @actionid
       END
 
       CLOSE cur
       DEALLOCATE cur
 
After trigger execution the queue will have one or several messages waiting for other service to process them. Now you can view not processed messages in the queue:
select * from dbo.<Queue name>

If you want to clear the queue, you have to execute the following script:

declare @conversation uniqueidentifier
while exists (select * from <Queue name> )
begin
      set @conversation = (select top 1 conversation_handle from <Queue name> )
      end conversation @conversation with cleanup
end

Also, it is important to say, that when you deactivate the queue, the messages will still be received but not processed, and when you activate the queue they all will appear in the queue.

Добавить комментарий

Ваш адрес email не будет опубликован.