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

2024-08-31 00:52:15

字體：大中小

來源：轉載

供稿：網友

在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 IDENTITY        PRIMARY KEY,    table_name sysname,    trigger_name sysname) -- b. 登記訂閱異步觸發器的存儲過程CREATE TABLE dbo.tb_async_trigger_subscriber(    ID int IDENTITY        PRIMARY KEY,    procedure_name sysname) -- c. 異步觸發器和存儲過程之間的訂閱關系CREATE TABLE dbo.tb_async_trigger_subscribtion(    trigger_id int        REFERENCES dbo.tb_async_trigger(            ID),    procedure_id int        REFERENCES 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    WITH        ENCRYPTION = 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 = CASE                            WHEN message_type_name = N'MSGT_async_trigger'                                THEN CONVERT(xml, message_body)                            ELSE NULL                        END        FROM 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. 調用異步觸發器訂閱的存儲過程        ;WITH        SUB 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 SUB                    WHERE table_name = @table_name                        AND trigger_name = @trigger_name                    FOR 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 IDENTITY        PRIMARY KEY,    col int)-- b. 同步的目的表CREATE TABLE dbo.t2(    id int IDENTITY        PRIMARY KEY,    col int)-- c. 記錄操作的日志表CREATE TABLE dbo.tb_log(    id int IDENTITY        PRIMARY 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 1                            OBJECT_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        ;WITH        I 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.col        FROM dbo.t2 A, I, D        WHERE A.ID = I.ID            AND I.ID = D.ID    END    ELSE            -- 插入    BEGIN        SET IDENTITY_INSERT dbo.t2 ON        ;WITH        I 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, col        FROM I        SET 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 = CASE                    WHEN @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

上一篇：在SQL Server2005中用語句創建數據庫和表

下一篇：講解SQL Server 2005數據庫的同義詞Bug