SQL Server - Como sincronizar dados de uma tabela entre bases diferentes utilizando Trigger

Visualizações: 3.272 views

Neste artigo

Tempo de Leitura: 4 minutos

Fala pessoal!
Nesse post de hoje eu vou trazer uma solução bem simples, mas que resolveu um problema de uma pessoa que me pediu ajuda para sincronizar dados de uma tabela entre bases diferentes, na mesma instância, da forma mais simples possível, onde você insere, atualiza ou apaga dados da tabela de origem e todas as alterações são replicadas para uma tabela espelho em outra base.

Como o foco da solução é ser rápida, simples, funcionar em qualquer versão e edição do SQL Server, não exigir um Job para ficar atualizando, tendo os dados atualizados quase que eu tempo real e são apenas algumas poucas tabelas ao invés da base inteira, pensei que a melhor solução para esse cenário é utilizar Triggers.

Existem outras soluções para resolver esse problema de replicar dados entre bases diferentes? Com certeza!

Change Data Capture: É rápido, é simples de usar, mas não funciona em qualquer edição do SQL Server e armazena todas as alterações numa base de histórico (sem necessidade nesse caso).
Temporal Tables: É rápido, é simples, mas só funciona a partir do SQL Server 2016, vai exigir um job para sincronizar os dados
Always On availability group: Não será fácil de implementar e nem rápido. Não vai replicar apenas algumas tabelas e sim a base toda e não funciona em todas as versões do SQL Server
Replicação: Não será fácil de implementar e nem rápido, mas atende aos requisitos de funcionar em todas as versões e edições do SQL Server, não exige um job, os dados são atualizados quase que em tempo real e dá para replicar apenas algumas tabelas

Criação das tabelas origem e espelho

IF (OBJECT_ID('Base1.dbo.Vendas') IS NOT NULL) DROP TABLE Base1.dbo.Vendas
CREATE TABLE Base1.dbo.Vendas (
    Id_Pedido INT IDENTITY(1,1),
    Dt_Pedido DATETIME,
    [Status] INT,
    Quantidade INT,
    Valor NUMERIC(18, 2)
)
GO

IF (OBJECT_ID('Base2.dbo.Vendas') IS NOT NULL) DROP TABLE Base2.dbo.Vendas
CREATE TABLE Base2.dbo.Vendas (
    Id_Pedido INT, -- Lembre de tirar o IDENTITY na tabela espelhada, se tiver
    Dt_Pedido DATETIME,
    [Status] INT,
    Quantidade INT,
    Valor NUMERIC(18, 2)
)
GO

IF (OBJECT_ID('Base1.dbo.Vendas') IS NOT NULL) DROP TABLE Base1.dbo.Vendas

CREATE TABLE Base1.dbo.Vendas (

Id_Pedido INT IDENTITY(1,1),

Dt_Pedido DATETIME,

[Status] INT,

Quantidade INT,

Valor NUMERIC(18, 2)

)

IF (OBJECT_ID('Base2.dbo.Vendas') IS NOT NULL) DROP TABLE Base2.dbo.Vendas

CREATE TABLE Base2.dbo.Vendas (

Id_Pedido INT, -- Lembre de tirar o IDENTITY na tabela espelhada, se tiver

Dt_Pedido DATETIME,

[Status] INT,

Quantidade INT,

Valor NUMERIC(18, 2)

)

Trigger que vai espelhar a tabela original

Com a trigger abaixo, vou replicar todas as operações de INSERT, UPDATE e DELETE feitas na tabela original para a tabela espelho.

Lembre-se de que se você alterar a estrutura da tabela de origem, como uma nova coluna, remover/renomear colunas existentes, por exemplo, você deverá sempre aplicar as mesmas alterações na tabela espelhada. Caso contrário, isso irá gerar erros na execução da trigger, a não ser que você altere o código da trigger para especificar exatamente o nome das colunas no INSERT e inclua as alterações também no UPDATE.

CREATE TRIGGER [trgEspelhoVendas] ON Base1.dbo.Vendas
AFTER INSERT, UPDATE, DELETE -- TRIGGER AFTER para evitar erros na escrita por erro na trigger
AS
BEGIN
    
    SET NOCOUNT ON

    -- UPDATE
    IF (EXISTS(SELECT * FROM Inserted) AND EXISTS (SELECT * FROM Deleted))
    BEGIN
        
        UPDATE A
        SET
            A.Dt_Pedido = B.Dt_Pedido,
            A.[Status] = B.[Status],
            A.Quantidade = B.Quantidade,
            A.Valor = B.Valor
        FROM
            Base2.dbo.Vendas A
            JOIN Inserted B ON B.Id_Pedido = A.Id_Pedido
 
    END
    ELSE BEGIN
 
        -- INSERT
        IF (EXISTS(SELECT * FROM Inserted))
        BEGIN
 
            INSERT INTO Base2.dbo.Vendas
            SELECT *
            FROM Inserted
 
        END
        ELSE BEGIN -- DELETE
 
            DELETE A
            FROM 
                Base2.dbo.Vendas A
                JOIN Deleted B ON B.Id_Pedido = A.Id_Pedido
 
        END
 
    END

END

CREATE TRIGGER [trgEspelhoVendas] ON Base1.dbo.Vendas

AFTER INSERT, UPDATE, DELETE -- TRIGGER AFTER para evitar erros na escrita por erro na trigger

BEGIN

SET NOCOUNT ON

-- UPDATE

IF (EXISTS(SELECT * FROM Inserted) AND EXISTS (SELECT * FROM Deleted))

BEGIN

UPDATE A

SET

A.Dt_Pedido = B.Dt_Pedido,

A.[Status] = B.[Status],

A.Quantidade = B.Quantidade,

A.Valor = B.Valor

FROM

Base2.dbo.Vendas A

JOIN Inserted B ON B.Id_Pedido = A.Id_Pedido

END

ELSE BEGIN

-- INSERT

IF (EXISTS(SELECT * FROM Inserted))

BEGIN

INSERT INTO Base2.dbo.Vendas

SELECT *

FROM Inserted

END

ELSE BEGIN -- DELETE

DELETE A

FROM

Base2.dbo.Vendas A

JOIN Deleted B ON B.Id_Pedido = A.Id_Pedido

END

Testando a solução

Vamos agora testar se a trigger realmente está funcionando conforme o esperado.

Vou inserir 10 linhas aleatórias na tabela dbo.Vendas no database Base1:

INSERT INTO Base1.dbo.Vendas ( Dt_Pedido, [Status], Quantidade, Valor )
SELECT
    DATEADD(SECOND, (ABS(CHECKSUM(PWDENCRYPT(N''))) / 2147483647.0) * 199999999, '2015-01-01'),
    (ABS(CHECKSUM(PWDENCRYPT(N''))) / 2147483647.0) * 9,
    (ABS(CHECKSUM(PWDENCRYPT(N''))) / 2147483647.0) * 10,
    0.459485495 * (ABS(CHECKSUM(PWDENCRYPT(N''))) / 2147483647.0) * DATEDIFF(DAY, '2015-01-01', GETDATE())
GO 10

INSERT INTO Base1.dbo.Vendas ( Dt_Pedido, [Status], Quantidade, Valor )

SELECT

DATEADD(SECOND, (ABS(CHECKSUM(PWDENCRYPT(N''))) / 2147483647.0) * 199999999, '2015-01-01'),

(ABS(CHECKSUM(PWDENCRYPT(N''))) / 2147483647.0) * 9,

(ABS(CHECKSUM(PWDENCRYPT(N''))) / 2147483647.0) * 10,

0.459485495 * (ABS(CHECKSUM(PWDENCRYPT(N''))) / 2147483647.0) * DATEDIFF(DAY, '2015-01-01', GETDATE())

GO 10

Resultado:

Agora vou fazer um UPDATE na tabela original:

UPDATE Base1.dbo.Vendas
SET Valor = 9999
WHERE Id_Pedido = 1

UPDATE Base1.dbo.Vendas

SET Valor = 9999

WHERE Id_Pedido = 1

Resultado:

E por fim, terminar os testes com um DELETE na tabela original:

DELETE Base1.dbo.Vendas
WHERE Id_Pedido = 7

1 2	DELETE Base1.dbo.Vendas WHERE Id_Pedido = 7

Resultado:

ATENÇÃO: Essa trigger será executada a cada INSERT/UPDATE/DELETE realizado na tabela original. Isso quer dizer que se você executar uma operação de DML em massa, como inserir um bloco de linhas ou fazer um update na tabela toda, por exemplo, cada linha afetada irá disparar uma execução da trigger, o que pode deixar o processo em si muito mais lento. Tenha cuidado ao usar trigger de DML por conta disso.

E é isso aí, pessoal!
Espero que tenham gostado dessa dica supersimples, mas que pode ajudar bastante gente no dia a dia com uma solução simples, mas que funciona 🙂

Assinar blog por e-mail

Visualizações do Blog

Microsoft MVP

Arquivo de Posts

Categorias

Posts recentes

SQL Server – Como sincronizar dados de uma tabela entre bases diferentes utilizando Trigger

Criação das tabelas origem e espelho

Trigger que vai espelhar a tabela original

Testando a solução

You may also like...

1 Response

Deixe uma respostaCancelar resposta

Criação das tabelas origem e espelho

Trigger que vai espelhar a tabela original

Testando a solução

You may also like...

Habilitando OLE Automation via T-SQL no SQL Server

SQL Server – Como compactar e descompactar arquivos e diretórios utilizando 7-zip e xp_cmdshell ou SQLCLR (C#)

SQL Server – Como identificar e coletar informações de consultas demoradas utilizando Trace (SQL Server Profiler)

1 Response

Deixe uma respostaCancelar resposta