Início / dba / Perguntas / 120073
Accepted
JohnG
Asked: 2015-11-05 07:25:08 +0800 CST

Gatilho para criar uma tabela de variáveis ​​a ser enviada ao service broker

  • 772

Eu configurei um service broker pela primeira vez e testei... Parece bom. Toda vez que uma operação DML acontece em uma tabela específica; Digamos a Tabela de Preços, uma trigger chama uma stored procedure passando para ela as tabelas inseridas e deletadas como parâmetros XML que vão enviar a mensagem (contendo os 2 XMLs) para a fila.

O procedimento de ativação processa a fila e insere as alterações feitas na tabela inicial em uma tabela de auditoria... Funciona muito bem.

O Problema ocorre quando a atualização da tabela Price é massiva. No meu caso, testei uma atualização de alteração de preço em 105.000 itens. As variáveis ​​XML inseridas e excluídas contêm 1,3 milhão de linhas cada... O que causa grandes problemas: TempDB cresce infinitamente e o processador sobe para 95%, o que é compreensível... mas nunca parece concluir a operação.

O código para o Trigger é Price_TAB_Audit_trig . O gatilho chama o procedimento SendMsgServiceBroker para enviar a mensagem para a Fila. Veja os comentários abaixo para o procedimento de ativação.

Estou tentando descobrir como posso dividir as variáveis ​​inseridas e excluídas (mensagens) em XMLs menores. Minhas perguntas/pensamentos são: Posso criar variáveis ​​de tabela e passar isso na mensagem em vez de XML? Se sim, como criar tabelas de variáveis ​​em uma trigger? Devo criar tabelas temporárias exclusivas globais e referenciá-las na mensagem?

Qualquer outra sugestão, é bem vinda. Desde já agradeço JG

sql-server trigger

2 respostas

  1. Best Answer

    Você pode enviar os dados xml em blocos predefinidos usando algo como o gatilho na configuração a seguir.

    Crie o testbed no 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
);

    O gatilho:

    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

    Insira alguns dados de teste para ver o que o gatilho faz:

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

    Mostrar resultados:

    SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerTestTarget;

DELETE TOP(100)
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerTestTarget;
    • 1

  2. A título de exemplo para usar um parâmetro com valor de tabela para passar os detalhes em seu procedimento armazenado, dê uma olhada nisso:

    Limpe os objetos do ambiente de teste, se eles já existirem:

    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;

    Crie objetos de teste:

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

    Teste o gatilho/processo armazenado:

    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;

    Veja o que está nas tabelas de destino: 

    SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerTestTarget;

    Isso provavelmente ainda será bastante problemático se o espaço for um prêmio e você decidir atualizar muitas linhas, pois o SQL Server precisará alocar espaço na variável de tabela para todos os dados presentes em insertede deleted.

    No entanto, esta é uma maneira elegante de abordar o problema, pelo menos do ponto de vista da legibilidade do código.

    • 1

relate perguntas