USE TEST
GO
--建立消息类型
/****** Object: MessageType [XMLMessageType] Script Date: 2012/8/30 15:05:56 ******/
CREATE MESSAGE TYPE [XMLMessageType] VALIDATION = WELL_FORMED_XML
GO
--建立约定
/****** Object: ServiceContract [XMLMessageContract] Script Date: 2012/8/30 15:06:20 ******/
CREATE CONTRACT [XMLMessageContract] ([XMLMessageType] SENT BY INITIATOR)
GO
--建立队列
/****** Object: ServiceQueue [dbo].[Queue_Argot] Script Date: 2012/8/30 15:06:44 ******/
CREATE QUEUE [dbo].[Queue_Argot] WITH STATUS = ON , RETENTION = OFF ON [PRIMARY]
GO
--建立服务
/****** Object: BrokerService [Service_Argot] Script Date: 2012/8/30 15:07:06 ******/
CREATE SERVICE [Service_Argot] ON QUEUE [dbo].[Queue_Argot] ([XMLMessageContract])
GO
--建立表删除新增触发器,并发送消息到队列
/****** Object: Trigger [dbo].[TR_NDAS_tb_Argot_DeleteAdd] Script Date: 2012/8/30 15:16:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TR_NDAS_tb_Argot_DeleteAdd]
ON [dbo].[NDAS_tb_Argot]
FOR INSERT,DELETE
AS
BEGIN
IF @@ROWCOUNT=0
RETURN
SET NOCOUNT ON
-- 将要发送的数据生成xml 数据
DECLARE
@message xml
IF EXISTS ( SELECT 1
FROM INSERTED )
BEGIN
SET @message = ( SELECT Operation = 'INSERTED'
,*
FROM INSERTED
FOR
XML RAW('ROW') ,
ROOT('ROOT')
)
END
IF EXISTS ( SELECT 1
FROM DELETED )
BEGIN
SET @message = ( SELECT Operation = 'DELETED'
,*
FROM DELETED
FOR
XML RAW('ROW') ,
ROOT('ROOT')
)
END
DECLARE
@handle uniqueidentifier
BEGIN DIALOG CONVERSATION @handle
FROM SERVICE [Service_Argot]
TO SERVICE N'Service_Argot'
ON CONTRACT XMLMessageContract
WITH
ENCRYPTION = OFF;
SEND
ON CONVERSATION @handle
MESSAGE TYPE XMLMessageType(
@message);
-- 消息发出即可, 不需要回复, 因此发出后即可结束会话
--END CONVERSATION @handle
END
GO
SERVICE BROKER创建异步触发器实例
最新推荐文章于 2021-12-16 11:39:59 发布