在SQL Server 2005中實現異步觸發器架構

在SQL Server 2005數據庫中，通過新增的Service Broker可以實現異步觸發器的處理功能。本文提供一種使用Service Broker實現的通用異步觸發器方法。

在本這個方法中，通過Service Broker構造異步觸發器處理架構，對于要使用這種架構的表，只需要創建相應的觸發器及處理觸發器中數據的存儲過程，并且在異步觸發器架構中登記觸發器和處理的存儲過程即可。如果一個觸發器中的數據要被多個表使用，只需要在dbo.tb_async_trigger_subscribtion中登記相應處理數據的存儲過程即可，即一個表的數據變更可以被多個表訂閱（使用）。

架構的步驟如下：

1． 數據庫配置

需要配置數據庫以允許使用Service Broker。本文以tempdb庫為例，故配置均在tempdb上下文中進行。

USE tempdbGO -- 允許Service BrokerALTER DATABASE tempdb SETENABLE_BROKERGO

2． 構建異步觸發器相關的對象

下面的T-SQL創建異步觸發器處理架構相關的對象。

-- =======================================-- 異步觸發器對象-- 1. service broker 對象-- =======================================-- a. message type, 要求使用xml 傳遞數據CREATE MESSAGE TYPE MSGT_async_triggerVALIDATION = WELL_FORMED_XMLGO -- b. 只需要發送消息CREATE CONTRACT CNT_async_trigger( MSGT_async_trigger SENT BY INITIATOR)GO -- c. 存儲消息的隊列CREATE QUEUE dbo.Q_async_triggerGO -- d. 用于消息處理的服務CREATE SERVICE SRV_async_trigger ON QUEUE dbo.Q_async_trigger(CNT_async_trigger)GO -- =======================================-- 異步觸發器對象-- 2. 異步觸發器處理的對象-- =======================================-- a. 登記異步觸發器的表CREATE TABLE dbo.tb_async_trigger( ID int IDENTITYPRIMARY KEY, table_name sysname, trigger_name sysname) -- b. 登記訂閱異步觸發器的存儲過程CREATE TABLE dbo.tb_async_trigger_subscriber( ID int IDENTITYPRIMARY KEY, procedure_name sysname) -- c. 異步觸發器和存儲過程之間的訂閱關系CREATE TABLE dbo.tb_async_trigger_subscribtion( trigger_id intREFERENCES dbo.tb_async_trigger( ID), procedure_id intREFERENCES dbo.tb_async_trigger_subscriber( ID), PRIMARY KEY(trigger_id, procedure_id))GO -- d. 發送消息的存儲過程CREATE PROC dbo.p_async_trigger_send @message xmlASSET NOCOUNT ONDECLARE @handle uniqueidentifierBEGIN DIALOG CONVERSATION @handle FROM SERVICE [SRV_async_trigger] TO SERVICE N'SRV_async_trigger' ON CONTRACT CNT_async_trigger WITHENCRYPTION = OFF;SEND ON CONVERSATION @handle MESSAGE TYPE MSGT_async_trigger(@message);-- 消息發出即可, 不需要回復, 因此發出后即可結束會話END CONVERSATION @handleGO -- e. 處理異步觸發器發送的消息CREATE PROC dbo.p_async_trigger_processASSET NOCOUNT ONDECLARE @handle uniqueidentifier, @message xml, @rows intSET @rows = 1WHILE @rows > 0BEGIN -- 處理已經收到的消息 WAITFOR(RECEIVE TOP(1) @handle = conversation_handle, @message = CASEWHEN message_type_name = N'MSGT_async_trigger' THEN CONVERT(xml, message_body)ELSE NULL ENDFROM dbo.Q_async_trigger ), TIMEOUT 10 SET @rows = @@ROWCOUNT IF @rows > 0 BEGIN-- 結束會話END CONVERSATION @handle; -- 處理消息-- a. 取發送者信息DECLARE @table_name sysname, @trigger_name sysname, @sql nvarchar(max)SELECT @table_name = @message.value('(/root/table_name)[1]', 'sysname'), @trigger_name = @message.value('(/root/trigger_name)[1]', 'sysname') -- b. 調用異步觸發器訂閱的存儲過程;WITHSUB AS( SELECT TR.table_name, TR.trigger_name, SUB.procedure_name FROM dbo.tb_async_trigger TR, dbo.tb_async_trigger_subscriber SUB, dbo.tb_async_trigger_subscribtion TRSUB WHERE TRSUB.trigger_id = TR.ID AND TRSUB.procedure_id = SUB.ID)SELECT @sql = (SELECT N'EXEC ' + procedure_name + N' @message'FROM SUBWHERE table_name = @table_name AND trigger_name = @trigger_nameFOR XML PATH(''), ROOT('r'), TYPE ).value('(/r)[1]', 'nvarchar(max)')EXEC sp_executesql @sql, N'@message xml', @message ENDENDGO -- f. 綁定處理的存儲過程到隊列ALTER QUEUE dbo.Q_async_trigger WITH ACTIVATION(STATUS = ON,PROCEDURE_NAME = dbo.p_async_trigger_process,MAX_QUEUE_READERS = 10,EXECUTE AS OWNER)GO

3． 使用示例

下面的T-SQL演示使用異步觸發器構架。示例中創建了三個表：

Dbo.t1 這個是源表，此表的數據變化將用于其他表

Dbo.t2 這個表要求保持與dbo.t1同步

Dbo.tb_log 這個表記錄dbo.t1中的數據變化情況

觸發器 TR_async_trigger 用于將表Dbo.t1中的數據變化發送到異步觸發器構架中。dbo.p_Sync_t1_t2和dbo.p_Record_log用于處理dbo.t1于中變化的數據。

在處理時，需要把相關的信息登記到異步觸發器架構的表中。

-- =======================================-- 3. 使用示例-- =======================================-- ===============================-- 測試對象-- a. 源表CREATE TABLE dbo.t1( id int IDENTITYPRIMARY KEY, col int)-- b. 同步的目的表CREATE TABLE dbo.t2( id int IDENTITYPRIMARY KEY, col int)-- c. 記錄操作的日志表CREATE TABLE dbo.tb_log( id int IDENTITYPRIMARY KEY, user_name sysname, operate_type varchar(10), inserted xml, deleted xml)GO -- a. 異步發送處理消息的觸發器CREATE TRIGGER TR_async_triggerON dbo.t1FOR INSERT, UPDATE, DELETEASIF @@ROWCOUNT = 0 RETURN SET NOCOUNT ON -- 將要發送的數據生成xml 數據DECLARE @message xmlSELECT @message = ( SELECT table_name = ( SELECT TOP 1OBJECT_NAME(parent_object_id) FROM sys.objects WHERE object_id = @@PROCID), trigger_name = OBJECT_NAME(@@PROCID), user_name = SUSER_SNAME(), inserted = ( SELECT * FROM inserted FOR XML AUTO, TYPE), deleted = ( SELECT * FROM deleted FOR XML AUTO, TYPE) FOR XML PATH(''), ROOT('root'), TYPE)-- 發送消息EXEC dbo.p_async_trigger_send @message = @messageGO -- b. 處理異步觸發器的存儲過程-- b.1 同步到t2 的存儲過程CREATE PROC dbo.p_Sync_t1_t2 @message xmlASSET NOCOUNT ONDECLARE @inserted bit, @deleted bitSELECT @inserted = @message.exist('/root/inserted'), @deleted = @message.exist('/root/deleted')IF @inserted = 1 IF @deleted = 1 -- 更新 BEGIN;WITHI AS( SELECT id = T.c.value('@id[1]', 'int'), col = T.c.value('@col[1]', 'int') FROM @message.nodes('/root/inserted/inserted') T(c)),D AS( SELECT id = T.c.value('@id[1]', 'int'), col = T.c.value('@col[1]', 'int') FROM @message.nodes('/root/deleted/deleted') T(c))UPDATE A SET col = I.colFROM dbo.t2 A, I, DWHERE A.ID = I.ID AND I.ID = D.ID END ELSE -- 插入 BEGINSET IDENTITY_INSERT dbo.t2 ON;WITHI AS( SELECT id = T.c.value('@id[1]', 'int'), col = T.c.value('@col[1]', 'int') FROM @message.nodes('/root/inserted/inserted') T(c))INSERT dbo.t2( id, col)SELECT id, colFROM ISET IDENTITY_INSERT dbo.t2 OFF ENDELSE -- 刪除BEGIN ;WITH D AS(SELECT id = T.c.value('@id[1]', 'int'), col = T.c.value('@col[1]', 'int')FROM @message.nodes('/root/deleted/deleted') T(c) ) DELETE A FROM dbo.t2 A, D WHERE A.ID = D.IDENDGO -- b.2 記錄操作記錄到dbo.tb_log 的存儲過程CREATE PROC dbo.p_Record_log @message xmlASSET NOCOUNT ONDECLARE @inserted bit, @deleted bitSELECT @inserted = @message.exist('/root/inserted'), @deleted = @message.exist('/root/deleted')INSERT dbo.tb_log( user_name, operate_type, inserted, deleted)SELECT @message.value('(/root/user_name)[1]', 'sysname'), operate_type = CASEWHEN @inserted = 1 AND @deleted = 1 THEN 'update'WHEN @inserted = 1 THEN 'insert'WHEN @deleted = 1 THEN 'delete' END, @message.query('/root/inserted'), @message.query('/root/deleted')GO -- ===============================-- 在異步觸發器處理系統中登記對象INSERT dbo.tb_async_trigger( table_name, trigger_name)VALUES( N't1', N'TR_async_trigger') INSERT dbo.tb_async_trigger_subscriber( procedure_name)SELECT N'dbo.p_Sync_t1_t2' UNION ALLSELECT N'dbo.p_Record_log' INSERT dbo.tb_async_trigger_subscribtion( trigger_id, procedure_id)SELECT 1, 1 UNION ALLSELECT 1, 2GO

4.使用測試

下面的T-SQL修改表dbo.t1中的數據，并檢查dbo.t2、dbo.tb_log中的數據，以確定異步觸發器架構的工作是否成功。

執行完成后可以看到dbo.t2、dbo.tb_log中有相關的記錄。

-- ===============================-- 測試INSERT dbo.t1SELECT 1 UNION ALLSELECT 2 UPDATE dbo.t1 SET col = 2WHERE id = 1 DELETE dbo.t1WHERE id = 2 -- 顯示結果WAITFOR DELAY '00:00:05' -- 延遲5 分鐘, 以便有時間處理消息(因為是異步的)SELECT * FROM dbo.t2SELECT * FROM dbo.tb_logGO

5.使用測試

下面的T-SQL刪除本文中建立的所有對象。

-- =======================================-- 5. 刪除相關的對象-- =======================================-- a. 刪除service broker 對象DROP SERVICE SRV_async_triggerDROP QUEUE dbo.Q_async_triggerDROP CONTRACT CNT_async_triggerDROP MESSAGE TYPE MSGT_async_triggerGO -- b. 刪除異步觸發器處理的相關對象DROP PROC dbo.p_async_trigger_processDROP PROC dbo.p_async_trigger_sendDROP TABLE dbo.tb_async_trigger_subscribtionDROP TABLE dbo.tb_async_trigger_subscriberDROP TABLE dbo.tb_async_triggerGO -- c. 刪除測試的對象DROP TABLE dbo.tb_log, dbo.t1, dbo.t2DROP PROC dbo.p_Sync_t1_t2, dbo.p_Record_log