Trigger to create a variable Table to be sent to service broker

Question

I've setup a service broker for the first time and tested it... Looks good. Everytime a DML operation happens on a specific table; Let's say the Price Table, a trigger calls a stored procedure passing it the inserted and deleted tables as XML parameters which will send the message (containing the 2 XMLs) to the queue.

The activation procedure then processes the queue, and inserts the changes done to the initial table into an audit table... Works Great.

The Problem occurs when the update on the Price table is a massive one. In my case, I tested a Price change update on 105 000 Items. The XML Inserted and Deleted Variables, contain 1.3 million lines each... Which causes major issues: TempDB grows infinitely and the processor goes up to 95% which is understandable... but never seems to complete the operation.

The code for the Trigger is Price_TAB_Audit_trig. The trigger calls the SendMsgServiceBroker procedure to send the message to the Queue. See comments below for activation procedure.

I'm trying to figure out how I can break down the inserted and deleted variables (messages) into smaller XMLs. My questions/Thoughts are: Can I create table variables and pass that in the message instead of XML? If so, how to create variable tables in a trigger? Should I create global unique temp tables and reference them in the message?

Any other suggestion, is welcome. Thank you in advance JG

Answer 1

You could send the xml data in predefined chunks by using something like the trigger in the following setup.

Create the testbed in 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
);

The trigger:

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 some test data to see what the trigger does:

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

Show results:

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerTestTarget;

DELETE TOP(100)
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerTestTarget;

Answer 2

By way of an example for using a table-valued-parameter to pass the details into your stored proc, take a look at this:

Cleanup the test-bed objects, if they already exist:

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;

Create test-bed objects:

/*
    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

Test the trigger/stored proc:

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;

See what is in the target tables:

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerTestTarget;

This will still probably be fairly problematic if space is a premium and you decide to update a lot of rows, since SQL Server will need to allocate space in the table variable for all the data present in inserted and deleted.

This is however an elegant way to approach the problem, at least from a code-readability perspective.