| 導購 | 订阅 | 在线投稿
分享
 
 
 

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

來源:互聯網  2008-06-01 02:15:22  評論

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

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

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

架構的步驟如下:

1. 數據庫配置

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

USE tempdb

GO

-- 允許Service Broker

ALTER DATABASE tempdb SET

ENABLE_BROKER

GO

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

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

-- =======================================

-- 異步觸發器對象

-- 1. service broker 對象

-- =======================================

-- a. message type, 要求使用xml 傳遞數據

CREATE MESSAGE TYPE MSGT_async_trigger

VALIDATION = WELL_FORMED_XML

GO

-- b. 只需要發送消息

CREATE CONTRACT CNT_async_trigger(

MSGT_async_trigger SENT BY INITIATOR)

GO

-- c. 存儲消息的隊列

CREATE QUEUE dbo.Q_async_trigger

GO

-- 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 xml

AS

SET NOCOUNT ON

DECLARE

@handle uniqueidentifier

BEGIN 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 @handle

GO

-- e. 處理異步觸發器發送的消息

CREATE PROC dbo.p_async_trigger_process

AS

SET NOCOUNT ON

DECLARE

@handle uniqueidentifier,

@message xml,

@rows int

SET @rows = 1

WHILE @rows > 0

BEGIN

-- 處理已經收到的消息

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

END

END

GO

-- 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_trigger

ON dbo.t1

FOR INSERT, UPDATE, DELETE

AS

IF @@ROWCOUNT = 0

RETURN

SET NOCOUNT ON

-- 將要發送的數據生成xml 數據

DECLARE

@message xml

SELECT

@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 = @message

GO

-- b. 處理異步觸發器的存儲過程

-- b.1 同步到t2 的存儲過程

CREATE PROC dbo.p_Sync_t1_t2

@message xml

AS

SET NOCOUNT ON

DECLARE

@inserted bit,

@deleted bit

SELECT

@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

END

ELSE -- 刪除

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.ID

END

GO

-- b.2 記錄操作記錄到dbo.tb_log 的存儲過程

CREATE PROC dbo.p_Record_log

@message xml

AS

SET NOCOUNT ON

DECLARE

@inserted bit,

@deleted bit

SELECT

@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 ALL

SELECT N'dbo.p_Record_log'

INSERT dbo.tb_async_trigger_subscribtion(

trigger_id, procedure_id)

SELECT 1, 1 UNION ALL

SELECT 1, 2

GO

4.使用測試

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

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

-- ===============================

-- 測試

INSERT dbo.t1

SELECT 1 UNION ALL

SELECT 2

UPDATE dbo.t1 SET

col = 2

WHERE id = 1

DELETE dbo.t1

WHERE id = 2

-- 顯示結果

WAITFOR DELAY '00:00:05'

-- 延遲5 分鍾, 以便有時間處理消息(因爲是異步的)

SELECT * FROM dbo.t2

SELECT * FROM dbo.tb_log

GO

5.使用測試

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

-- =======================================

-- 5. 刪除相關的對象

-- =======================================

-- a. 刪除service broker 對象

DROP SERVICE SRV_async_trigger

DROP QUEUE dbo.Q_async_trigger

DROP CONTRACT CNT_async_trigger

DROP MESSAGE TYPE MSGT_async_trigger

GO

-- b. 刪除異步觸發器處理的相關對象

DROP PROC dbo.p_async_trigger_process

DROP PROC dbo.p_async_trigger_send

DROP TABLE dbo.tb_async_trigger_subscribtion

DROP TABLE dbo.tb_async_trigger_subscriber

DROP TABLE dbo.tb_async_trigger

GO

-- c. 刪除測試的對象

DROP TABLE dbo.tb_log, dbo.t1, dbo.t2

DROP PROC dbo.p_Sync_t1_t2, dbo.p_Record_log

在SQL Server 2005中實現異步觸發器架構: 在SQL Server 2005數據庫中,通過新增的Service Broker可以實現異步觸發器的處理功能。本文提供一種使用Service Broker實現的通用異步觸發器方法。 在本這個方法中,通過Service Broker構造異步觸發器處理架構,對于要使用這種架構的表,只需要創建相應的觸發器及處理觸發器中數據的存儲過程,並且在異步觸發器架構中登記觸發器和處理的存儲過程即可。如果一個觸發器中的數據要被多個表使用,只需要在dbo.tb_async_trigger_subscribtion中登記相應處理數據的存儲過程即可,即一個表的數據變更可以被多個表訂閱(使用)。 架構的步驟如下: 1. 數據庫配置 需要配置數據庫以允許使用Service Broker。本文以tempdb庫爲例,故配置均在tempdb上下文中進行。 USE tempdb GO -- 允許Service Broker ALTER DATABASE tempdb SET ENABLE_BROKER GO 2. 構建異步觸發器相關的對象 下面的T-SQL創建異步觸發器處理架構相關的對象。 -- ======================================= -- 異步觸發器對象 -- 1. service broker 對象 -- ======================================= -- a. message type, 要求使用xml 傳遞數據 CREATE MESSAGE TYPE MSGT_async_trigger VALIDATION = WELL_FORMED_XML GO -- b. 只需要發送消息 CREATE CONTRACT CNT_async_trigger( MSGT_async_trigger SENT BY INITIATOR) GO -- c. 存儲消息的隊列 CREATE QUEUE dbo.Q_async_trigger GO -- 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 xml AS SET NOCOUNT ON DECLARE @handle uniqueidentifier BEGIN 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 @handle GO -- e. 處理異步觸發器發送的消息 CREATE PROC dbo.p_async_trigger_process AS SET NOCOUNT ON DECLARE @handle uniqueidentifier, @message xml, @rows int SET @rows = 1 WHILE @rows > 0 BEGIN -- 處理已經收到的消息 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 END END GO -- 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_trigger ON dbo.t1 FOR INSERT, UPDATE, DELETE AS IF @@ROWCOUNT = 0 RETURN SET NOCOUNT ON -- 將要發送的數據生成xml 數據 DECLARE @message xml SELECT @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 = @message GO -- b. 處理異步觸發器的存儲過程 -- b.1 同步到t2 的存儲過程 CREATE PROC dbo.p_Sync_t1_t2 @message xml AS SET NOCOUNT ON DECLARE @inserted bit, @deleted bit SELECT @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 END ELSE -- 刪除 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.ID END GO -- b.2 記錄操作記錄到dbo.tb_log 的存儲過程 CREATE PROC dbo.p_Record_log @message xml AS SET NOCOUNT ON DECLARE @inserted bit, @deleted bit SELECT @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 ALL SELECT N'dbo.p_Record_log' INSERT dbo.tb_async_trigger_subscribtion( trigger_id, procedure_id) SELECT 1, 1 UNION ALL SELECT 1, 2 GO 4.使用測試 下面的T-SQL修改表dbo.t1中的數據,並檢查dbo.t2、dbo.tb_log中的數據,以確定異步觸發器架構的工作是否成功。 執行完成後可以看到dbo.t2、dbo.tb_log中有相關的記錄。 -- =============================== -- 測試 INSERT dbo.t1 SELECT 1 UNION ALL SELECT 2 UPDATE dbo.t1 SET col = 2 WHERE id = 1 DELETE dbo.t1 WHERE id = 2 -- 顯示結果 WAITFOR DELAY '00:00:05' -- 延遲5 分鍾, 以便有時間處理消息(因爲是異步的) SELECT * FROM dbo.t2 SELECT * FROM dbo.tb_log GO 5.使用測試 下面的T-SQL刪除本文中建立的所有對象。 -- ======================================= -- 5. 刪除相關的對象 -- ======================================= -- a. 刪除service broker 對象 DROP SERVICE SRV_async_trigger DROP QUEUE dbo.Q_async_trigger DROP CONTRACT CNT_async_trigger DROP MESSAGE TYPE MSGT_async_trigger GO -- b. 刪除異步觸發器處理的相關對象 DROP PROC dbo.p_async_trigger_process DROP PROC dbo.p_async_trigger_send DROP TABLE dbo.tb_async_trigger_subscribtion DROP TABLE dbo.tb_async_trigger_subscriber DROP TABLE dbo.tb_async_trigger GO -- c. 刪除測試的對象 DROP TABLE dbo.tb_log, dbo.t1, dbo.t2 DROP PROC dbo.p_Sync_t1_t2, dbo.p_Record_log
󰈣󰈤
王朝萬家燈火計劃
期待原創作者加盟
 
 
 
>>返回首頁<<
 
 
 
 
 熱帖排行
 
 
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有