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.