主页 / dba / 问题 / 120073
Accepted
JohnG
Asked: 2015-11-05 07:25:08 +0800 CST

触发器创建要发送到服务代理的变量表

  • 772

我第一次设置了服务代理并对其进行了测试……看起来不错。每次在特定表上发生 DML 操作时；假设价格表，一个触发器调用一个存储过程，将插入和删除的表作为 XML 参数传递给它，这会将消息（包含 2 个 XML）发送到队列。

激活过程然后处理队列，并将对初始表所做的更改插入到审计表中......效果很好。

当价格表上的更新量很大时，就会出现问题。就我而言，我测试了 105 000 件商品的价格变化更新。XML Inserted 和 Deleted 变量，每个包含 130 万行......这会导致主要问题：TempDB 无限增长，处理器上升到 95%，这是可以理解的......但似乎从未完成操作。

触发器的代码是Price_TAB_Audit_trig。触发器调用SendMsgServiceBroker过程将消息发送到队列。有关激活过程，请参阅下面的评论。

我试图弄清楚如何将插入和删除的变量（消息）分解成更小的 XML。我的问题/想法是：我可以创建表变量并将其传递到消息而不是 XML 中吗？如果是这样，如何在触发器中创建变量表？我应该创建全局唯一临时表并在消息中引用它们吗？

任何其他建议，欢迎。提前谢谢你

sql-server trigger

2 个回答

  1. Best Answer

    您可以使用以下设置中的触发器之类的东西以预定义的块发送 xml 数据。

    在 tempdb 中创建测试平台：

    USE tempdb;

IF EXISTS (SELECT 1 FROM sys.triggers t WHERE t.name = 'TriggerTest_Chunked')
DROP TRIGGER dbo.TriggerTest_Chunked;


IF EXISTS(SELECT 1 FROM sys.tables t WHERE t.name = 'TriggerTest')
DROP TABLE dbo.TriggerTest;

CREATE TABLE dbo.TriggerTest
(
    ID INT NOT NULL
);

IF EXISTS(SELECT 1 FROM sys.tables t WHERE t.name = 'TriggerTestTarget')
DROP TABLE dbo.TriggerTestTarget;

CREATE TABLE dbo.TriggerTestTarget
(
    OperationType INT NOT NULL
    , DEETS XML NOT NULL
);

    触发：

    CREATE TRIGGER dbo.TriggerTest_Chunked
ON dbo.TriggerTest
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @RowStart INT;
    DECLARE @RowEnd INT;
    DECLARE @RowCount INT;
    DECLARE @BatchSize INT;
    SET @BatchSize = 1000;

    SET @RowStart = 0;

    SELECT @RowCount = COUNT(1) 
    FROM inserted;

    IF @RowCount > @BatchSize 
        SET @RowEnd = @BatchSize
    ELSE
        SET @RowEnd = @RowCount;

    WHILE @RowEnd <= @RowCount AND (@RowStart < @RowEnd)
    BEGIN
        INSERT INTO dbo.TriggerTestTarget (OperationType, DEETS)
        SELECT 1, (
            SELECT ID
            FROM (
                    SELECT *
                        , rn = ROW_NUMBER() OVER (ORDER BY ID)
                    FROM inserted
                ) i_rn
            WHERE i_rn.rn > @RowStart
                AND i_rn.rn <= @RowEnd
            FOR XML PATH('')
        );

        SET @RowStart = @RowStart + @BatchSize;
        IF @RowEnd + @BatchSize > @RowCount
            SET @BatchSize = @RowCount - @RowEnd;
        SET @RowEnd = @RowEnd + @BatchSize;
    END

    SET @RowStart = 0;

    SELECT @RowCount = COUNT(1) 
    FROM deleted;

    IF @RowCount > @BatchSize 
        SET @RowEnd = @BatchSize
    ELSE
        SET @RowEnd = @RowCount;

    WHILE @RowEnd <= @RowCount AND (@RowStart < @RowEnd)
    BEGIN
        INSERT INTO dbo.TriggerTestTarget (OperationType, DEETS)
        SELECT 2, (
            SELECT ID
            FROM (
                    SELECT *
                        , rn = ROW_NUMBER() OVER (ORDER BY ID)
                    FROM deleted
                ) i_rn
            WHERE i_rn.rn > @RowStart
                AND i_rn.rn <= @RowEnd
            FOR XML PATH('')
        );

        SET @RowStart = @RowStart + @BatchSize;
        IF @RowEnd + @BatchSize > @RowCount
            SET @BatchSize = @RowCount - @RowEnd;
        SET @RowEnd = @RowEnd + @BatchSize;
    END
END
GO

    插入一些测试数据以查看触发器的作用：

    INSERT INTO dbo.TriggerTest (ID)
SELECT ROW_NUMBER() OVER (ORDER BY o1.object_id, o2.object_id) 
FROM sys.objects o1
    , sys.objects o2;

    显示结果：

    SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerTestTarget;

DELETE TOP(100)
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerTestTarget;
    • 1

  2. 通过使用表值参数将详细信息传递到存储过程的示例，请看一下：

    清理测试台对象（如果它们已经存在）：

    USE tempdb;

IF EXISTS (SELECT 1 FROM sys.triggers t WHERE t.name = 'TriggerTest1')
DROP TRIGGER dbo.TriggerTest1;

IF EXISTS (SELECT 1 FROM sys.sql_modules sm 
    INNER JOIN sys.objects o ON sm.object_id = o.object_id 
    WHERE o.name = 'InsertFromTrigger')
DROP PROCEDURE dbo.InsertFromTrigger;

IF EXISTS (SELECT 1
    FROM sys.types t
    WHERE t.name = 'TriggerData'
)
DROP TYPE TriggerData;

IF EXISTS(SELECT 1 FROM sys.tables t WHERE t.name = 'TriggerTest')
DROP TABLE dbo.TriggerTest;

IF EXISTS(SELECT 1 FROM sys.tables t WHERE t.name = 'TriggerTestTarget')
DROP TABLE dbo.TriggerTestTarget;

    创建测试台对象：

    /*
    Creates a custom table-type definition
*/
CREATE TYPE TriggerData AS TABLE
(
    OperationType INT NOT NULL
    , ID INT NOT NULL
);

CREATE TABLE dbo.TriggerTest
(
    ID INT NOT NULL
);

CREATE TABLE dbo.TriggerTestTarget
(
    OperationType INT NOT NULL
    , ID INT NOT NULL
);
GO

/*
    create a procedure to accept data from the trigger
*/
CREATE PROCEDURE dbo.InsertFromTrigger
(
    @td AS TriggerData READONLY
)
AS
BEGIN
    INSERT INTO dbo.TriggerTestTarget
    SELECT *
    FROM @td;
END
GO


/*
    create a trigger to send data to the stored proc
*/
CREATE TRIGGER dbo.TriggerTest1
ON dbo.TriggerTest
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @t TriggerData;
    INSERT INTO @t (OperationType, ID)
    SELECT 1, *
    FROM inserted
    UNION ALL 
    SELECT 2, *
    FROM deleted;
    EXEC dbo.InsertFromTrigger @t;
END
GO

    测试触发器/存储过程：

    INSERT INTO dbo.TriggerTest
SELECT ROW_NUMBER() OVER (ORDER BY o1.object_id, o2.object_id)
FROM sys.objects o1
    , sys.objects o2;

DELETE TOP(50) 
FROM dbo.TriggerTest;

    查看目标表中的内容： 

    SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerTestTarget;

    如果空间很宝贵，并且您决定更新大量行，这可能仍然会带来相当大的问题，因为 SQL Server 需要在表变量中为inserted和中存在的所有数据分配空间deleted。

    然而，至少从代码可读性的角度来看，这是解决问题的一种优雅方式。

    • 1

相关问题