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

Post Views 3,272 views

Neste artigo

Reading time 4 minutes

Hey guys!
In this post today I'm going to bring a very simple solution, but that solved a problem of a person who asked me for help to synchronize data from a table between different bases, in the same instance, in the simplest possible way, where you insert, update or deletes data from the source table and all changes are replicated to a mirror table in another base.

As the focus of the solution is to be fast, simple, work in any version and edition of SQL Server, not requiring a Job to keep updating, having the data updated almost in real time and there are only a few tables instead of the entire base, I thought the best solution for this scenario is to use Triggers.

Are there other solutions to solve this problem of replicating data between different databases? For sure!

Change Data Capture: It's fast, it's simple to use, but it doesn't work on any edition of SQL Server and it stores all changes in a historical basis (no need in this case).
Temporal Tables: It's fast, it's simple, but it only works from SQL Server 2016, it will require a job to synchronize the data
Always On availability group: It will not be easy to implement and not fast. It won't replicate just a few tables but the whole base and it doesn't work in all versions of SQL Server
replication: It won't be easy to implement and it won't be fast, but it meets the requirement to work on all versions and editions of SQL Server, it doesn't require a job, the data is updated almost in real time and it can only replicate a few tables

Creating source and mirror tables

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 that will mirror the original table

With the trigger below, I will replicate all INSERT, UPDATE and DELETE operations done on the original table to the mirror table.

Remember that if you change the structure of the source table, like a new column, removing/renaming existing columns for example, you must always apply the same changes to the mirror table. Otherwise, this will generate errors in the trigger execution, unless you change the trigger code to specify exactly the column names in the INSERT and include the changes in the UPDATE as well.

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

Testing the solution

Let's now test if the trigger is really working as expected.

I'm going to insert 10 random rows into the dbo.Sales table in the Base1 database:

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

Result:

Now I'm going to UPDATE the original table:

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

UPDATE Base1.dbo.Vendas

SET Valor = 9999

WHERE Id_Pedido = 1

Result:

And finally, finish the tests with a DELETE on the original table:

DELETE Base1.dbo.Vendas
WHERE Id_Pedido = 7

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

Result:

ATTENTION: This trigger will be executed every INSERT/UPDATE/DELETE performed on the original table. This means that if you execute a bulk DML operation, such as inserting a block of rows or updating the entire table, for example, each affected row will trigger an execution of the trigger, which can leave the process itself very slower. Be careful when using DML triggers because of this.

And that's it, folks!
I hope you enjoyed this super simple tip, but it can help a lot of people on a daily basis with a simple solution that works 🙂

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

SQL Server - How to synchronize data from a table between different databases using Trigger

Creating source and mirror tables

Trigger that will mirror the original table

Testing the solution

You may also like...

1 Response

Deixe uma respostaCancelar resposta

Creating source and mirror tables

Trigger that will mirror the original table

Testing the solution

You may also like...

Ferramentas para SQL Server que todo DBA deveria conhecer – Live no SQL Norte

SQL Server – Como enviar e-mails pelo banco de dados utilizando CLR (C#)

SQL Server: Sessão executando há muito tempo com o comando sp_readrequest (DatabaseMail)

1 Response

Deixe uma respostaCancelar resposta