Hey guys!
In today's post I'm going to bring a very simple solution, but it solved a problem from someone who asked me for help to synchronize data from a table between different bases, in the same instance, in the simplest way possible, where you insert, update or delete 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 on any version and edition of SQL Server, not require a Job to keep updating, having the data updated almost in real time and there are just a few tables instead of the entire database, 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 stores all changes in a historical database (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 or quick. It won't replicate just a few tables, but the entire database and it doesn't work on all versions of SQL Server
  • Replication: It will not be easy to implement nor fast, but it meets the requirements of working in all versions and editions of SQL Server, it does not require a job, the data is updated almost in real time and it is possible to replicate just a few tables

Creation of 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

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, such as a new column, remove/renam existing columns, for example, you must always apply the same changes to the mirrored table. Otherwise, this will generate errors when executing the trigger, unless you change the trigger code to specify exactly the name of the columns in the INSERT and also include the changes in the 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

Testing the solution

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

I will insert 10 random rows into the dbo.Vendas 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

Result:

Now I'm going to do an UPDATE on the original table:

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

Result:

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

And that's it, folks!
I hope you liked this super simple tip, but it can help a lot of people in their daily lives with a simple solution, but it works 🙂