Триггер с Service Broker

Service Broker используется для организации канала обмена сообщениями между двумя объектами, другими словами, источником (инициатором) и целью. Сообщения используются для передачи данных и инициации обработки по получении сообщения. Цель и инициатор могут находиться в одной и той же базе данных либо в разных базах одного или разных экземплярах Database Engine.

Service Broker передает сообщения по протоколу "Dialog", который позволяет передавать сообщения в обе стороны. Dialog Protocol определяет логические шаги, необходимые для осуществления обмена сообщениями и обеспечения правильного порядка следования сообщений, такого, в котором они были отпрвлены.

create database AsyncProcessing
go

ALTER DATABASE AsyncProcessing set ENABLE_BROKER with ROLLBACK IMMEDIATE;
go

use AsyncProcessing
go

create table auditlog
(
 xmlstring xml
)
go

alter procedure [dbo].[spMessageProcTest]
as
begin
 declare
  @message_type varchar(100)
  ,@dialog uniqueidentifier
  ,@message_body XML
  ;
  while (1 = 1)
  begin -- получение следующего доступного сообщения из очереди
   waitfor
   (
    receive top(1)
     @message_type = message_type_name,   
     @message_body = cast(message_body as xml),   
     @dialog = conversation_handle
     from 
     dbo.ProcessingQueue
   )
   ,timeout 500   

   if
   (
    @@rowcount = 0
    or
    @message_body is null
   )
   begin
    break
   end
   else
   begin
       --обработка xml сообщений...
    insert into auditlog values(@message_body, getdate())
   end
  end conversation @dialog

    end
end
go

-- создание типа сообщения

create message type AsyncRequest
authorization dbo
validation = WELL_forMED_XML;

-- создание контракта

create CONTRACT AsyncContract
AUTHORIZATION dbo
(AsyncRequest SENT by INITIATOR);

-- создание очереди

create QUEUE dbo.ProcessingQueue with STATUS=on, ACTIVATIon
(STATUS = on, MAX_QUEUE_READERS = 1,
PROCEDURE_NAME = spMessageProcTest,   EXECUTE AS OWNER);

-- создание сервиса-инициатора

create SERVICE ProcessingServiceInitiator
authorization dbo
on QUEUE dbo.ProcessingQueue (AsyncContract);

-- создание целевого сервиса

create SERVICE [ProcessingServiceTarget]
authorization dbo
on QUEUE dbo.ProcessingQueue (AsyncContract);
go

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 into DepartmentMaster 
values 
(
 'Purchase'
 ,'Purchase Department'
),
 ('Sales','Sales Department'),
 ('Account','Account Department')
 go

--создание триггера на обновление

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 
    [ProcessingServiceInitiator] 
   TO SERVICE 
    'ProcessingServiceTarget' 
   on ConTRACT 
    [AsyncContract] 
   with ENCRYPTIon = off; 
  
   Send on ConVERSATIon @Handle 
    MESSAGE TYPE [AsyncRequest](@MessageBody);
 end
end