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.