Speak guys!
In this post today I'm going to bring a very simple solution, but it 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 way possible, where you insert, update or erases 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 require 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 any other solutions to solve this problem of replicating data between different databases? Certainly!
- 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 history base (no need in this case).
- Temporal Tables: It's fast, it's simple, but it only works from SQL Server 2016 onwards, it will require a job to sync the data
- Always On Availability Group: It won't be easy to implement and it won't be fast. It won't replicate just a few tables, but the entire base and it doesn't work on all versions of SQL Server
- Replication: It won't be easy to implement or fast, but it meets the requirements 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 you can replicate just a few tables
Creating the source and mirror tables
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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/rename existing columns, for example, you must always apply the same changes to the mirrored table. Otherwise, this will generate errors in 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
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 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:
|
1 2 3 4 5 6 7 |
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'll do an UPDATE on the original table:
|
1 2 3 |
UPDATE Base1.dbo.Vendas SET Valor = 9999 WHERE Id_Pedido = 1 |
Result:
And finally, finish the tests with a DELETE on the original table:
|
1 2 |
DELETE Base1.dbo.Vendas WHERE Id_Pedido = 7 |
Result:
And that's it folks!
I hope you liked this super simple tip, but it can help a lot of people in everyday life with a simple solution, but it works 🙂
I would like to know what to do if I don't have a main base, in case the change could come from any of the bases.