转： SQL Server 2005 中实现通用的异步触发器架构

最新推荐文章于 2024-10-16 21:01:31 发布
最新推荐文章于 2024-10-16 21:01:31 发布
阅读量460 收藏
点赞数
分类专栏： Microsoft SQL Server 文章标签： sql server table insert service xml go
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/china_aspx/article/details/2176351
版权

 SQL Server 2005 中实现通用的异步触发器架构   <script src="http://blog.csdn.net/count.aspx?ID=2161527&Type=Rank" type="text/javascript"> </script> 文章指数:0    CSDN Blog推出文章指数概念，文章指数是对Blog文章综合评分后推算出的，综合评分项分别是该文章的点击量，回复次数，被网摘收录数量，文章长度和文章类型；满分100，每月更新一次。

  

在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.t 2 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.t 2 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




Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=2161527

 

 
china_aspx
关注
专栏目录
教程：Java全栈知识架构体系总结
02-04
2. **MySQL数据库**：作为最广泛使用的SQL数据库之一，MySQL的学习包括SQL查询语句、数据库设计、事务处理、存储过程、触发器等。熟练掌握MySQL对于任何后端开发者都至关重要。 3. **Redis**：Redis是一个高性能的...
SQL利用扩展事件进行调优和
02-26
SQL Server中的扩展事件是一种强大的诊断工具，它允许数据库管理员和开发者监视各种事件以及活动，从而进行性能调优和故障排除。扩展事件在SQL Server 2008版本中首次引入，虽然当时没有图形界面支持，捕捉结果也...
经典数据库面试题整理首发
07-24
- **服务 Broker**：SQL Server特有的异步消息队列机制，用于跨数据库通信和任务调度。 - **触发器**：用于在数据修改前后执行自动操作，实现业务规则的强制。 3. **通用数据库知识**： - **ACID属性**：原子性...
ASP基于WEB网上聊天室设计(源代码+论文).rar
09-16
而SQL（Structured Query Language）则是一种更强大、更通用的关系型数据库管理系统，如SQL Server。 3. **数据库操作**：在聊天室应用中，常见的数据库操作包括用户注册、登录验证、发送消息、接收消息和存储聊天...
探索 Python 中的 XML 转换利器：xml2dict
AIGC搞起
10-16 472
在处理数据交换时，XML 是一种常用的格式，但它的复杂性和繁琐的处理方式常常让人头疼。想象一下，如果能有一种方法，能将 XML 转换为 Python 字典，那将大大提高我们的工作效率。这就是xml2dict库的用武之地，它能够将 XML 转换为字典，反之亦然，让数据的处理变得简单直观。xml2dict是一个强大的工具，它简化了 XML 数据的处理，使得在 Python 中操作 XML 数据变得轻松愉快。无论是从 XML 到字典的转换，还是字典到 XML 的转换，xml2dict都能提供简单而有效的解决方案。
Qt- JSON&XML
m0_74380601的博客
10-16 535
XML(Extensible Markup Language，可扩展标记语言)，是一种类似于HTML的标记语言html 都是系统预定义好的标签xml 都是自定义的标签XML是用来传输数据、保存数据，而不是显示数据。XML的标签没有被预定义，用户需要在使用时自行进行定义。XML是W3C（万维网联盟）的推荐标准。XML使用的树形结构更能表现出数据的包含关系Qt 提供了两种方案来处理 XML 文档：DOM 方式QXmlStreamReader 和 QXmlStreamWriter。
若依框架中根目录与子模块 `pom.xml` 的区别
wenxuankeji的博客
10-16 470
在使用 Maven 构建的多模块项目中，比如若依（RuoYi）这样的后台管理系统，我们会遇到两种不同作用的pom.xml文件：位于项目根目录下的以及每个子模块下的。这两者之间存在一些关键差异，并且理解这些差异对于正确配置和管理项目至关重要。了解并正确应用这两种pom.xml的特性，可以帮助开发者更好地组织和维护复杂的多模块项目。希望这篇博客能够帮助您加深对 Maven 项目结构的认识，并为您的开发工作提供一定的指导。如果您还有更多关于若依或其他相关技术的问题，欢迎继续探讨！根目录pom.xml。
MyBatis方法重载与XML中ID的重复性
2301_76419561的博客
10-16 210
MyBatis是一款优秀的持久层框架，它支持定制化SQL、存储过程以及高级映射。在使用MyBatis的过程中，开发者可能会遇到一些关于方法重载和XML映射文件中ID重复性的问题。下面将详细讲解这两个问题。
汽车管理系统——日销售清单xml
学习历程记录和分享
10-16 493
QListWidget 是 Qt 的一个控件，它提供了一个基于项（item-based）的列表，QListWidgetItem 就是列表中的每个项的类。若是空的时间节点，则创建一个，然后获取已有的节点的时间，判断，如果是今天的，就继续在该时间下创建子节点，如果时间与当前时间不对应，是昨天的，那就重新获取时间，重新创建时间节点。的作用是将 XML 声明添加到 XML 文档中，使得该文档符合标准的 XML 格式，明确规定文档的版本和字符编码。是的，在 C++ 中，枚举类型的枚举值实际上是整型常量。
毕业设计论文SpringBoot+Vue毕业生信息招聘平台.docx
10-16
毕业设计论文
PHP-006教务选课学生选课成绩管理系统毕业课程源码设计+论文资料
10-16
编号：306 作为php高校院务学生选课成绩管理系统，在系统中有学生信息和教师信息以及课程信息需要管理员分类管理。 (1)学生管理：管理员登录系统后可以添加学生，查询学生，修改学生，删除学生信息。 (2)教师管理：管理员登录系统后可以添加教师，查询教师，修改教师，删除教师信息。 (3)课程管理：管理员登录系统后可以登记新的课程信息，查询修改课程信息，删除课程信息. (4)授课信息管理：管理员登录系统后可以分配课程由哪个老师教授。 (5)班级信息管理：管理员可以在系统其他功能运行前先把班级信息设置好。 (6)选课管理：学生登录系统后可以对课程进行选择。 (7)成绩管理：教师登录系统后，可以查询自己教授的课程并对学生的成绩打分。 (8)系统管理： 修改登录密码，关于系统说明
在线教育视频&JAVA&基于SpringBoot的在线视频教育平台的设计与实现（毕业论文+开题）
10-16
本系统采用的数据库是Mysql，使用SpringBoot框架开发， 使用在线视频教育平台分为管理员和用户、教师三个角色的权限模块。 管理员所能使用的功能主要有：首页、个人中心、用户管理、教师管理、课程信息管理、课程类型管理、我的收藏管理、系统管理、订单管理等。 用户可以实现首页、个人中心、课程信息管理、我的收藏管理、订单管理等。 教师可以实现首页、个人中心、课程信息管理、我的收藏管理等。
个性化影片推荐系统.zip
最新发布
10-16
随着科学技术的飞速发展，社会的方方面面、各行各业都在努力与现代的先进技术接轨，通过科技手段来提高自身的优势，个性化影片推荐系统当然也不能排除在外。 个性化影片推荐系统，在个性化影片推荐系统可以查看首页、热门电影、新闻资讯、我的、跳转到后台、客服等内容
Uniapp发现的Bug，用于重现project_Uniapp Bug.zip
10-16
Uniapp发现的Bug，用于重现project_Uniapp Bug
FRIIS公式计算距离Matlab实现.rar
10-16
1.版本：matlab2014/2019a/2024a 2.附赠案例数据可直接运行matlab程序。 3.代码特点：参数化编程、参数可方便更改、代码编程思路清晰、注释明细。 4.适用对象：计算机，电子信息工程、数学等专业的大学生课程设计、期末大作业和毕业设计。
毕业设计论文Django+Vue学习系统.docx
10-16
毕业设计论文
行人重识别-用于行人重识别的稀疏标签平滑正则化优化-附项目源码+流程教程-优质项目实战.zip
10-16
行人重识别_用于行人重识别的稀疏标签平滑正则化优化_附项目源码+流程教程_优质项目实战
毕业设计论文SpringBoot+Vue宠物用品交易平台.docx
10-16
毕业设计论文
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值