Keep table records synchronized between two different databases

Question

We are performing a system migration, where some restructurings will be done in the current bank in a new bank. Basically I will have two banks BancoVelho and BancoNovo, having to keep both of them updated according to the mapping of the tables that have been restructured or not.

Then you would have to keep updated the following tables

CREATE TABLE [BANCO_VELHO].[dbo].[FRANQUIA_TIPO](
    [ID_FRANQUIA_TIPO] [int] IDENTITY(1,1) NOT NULL,
    [CODIGO] [varchar](10) NULL,
    [DESCRICAO] [varchar](100) NOT NULL,
    [ID_FRANQUIA_TIPO_MASTER] [int] NULL,   
    [DATA_HORA_CADASTRO_ALTERACAO] [datetime] NOT NULL,
    [ATIVO] [bit] NOT NULL,
    [ID_USUARIO_CADASTRO_ALTERACAO] [int] NOT NULL,
 CONSTRAINT [PK_FRANQUIA_TIPO] PRIMARY KEY CLUSTERED 
(
    [ID_FRANQUIA_TIPO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

--

CREATE TABLE [BancoNovo].[dbo].[FranquiaTipo](
    [FranquiaTipoId] [int] IDENTITY(1,1) NOT NULL,
    [Descricao] [varchar](100) NOT NULL,
    [FranquiaTipoMasterId] [int] NULL,
    [UsuarioCad] [varchar](100) NOT NULL,
    [DataHoraCad] [datetime] NOT NULL,
    [Ativo] [bit] NOT NULL,
 CONSTRAINT [PK_dbo.FranquiaTipo] PRIMARY KEY CLUSTERED 
(
    [FranquiaTipoId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

So I’m thinking of creating a Trigger in the bank that does this job.

The new system that will be developed will be in ASP.NET MVC using EntityFramework.

Based on the idea of creating Trigger, I came to the next Trigger

-- =============================================
-- Author:      Tondolo, Pablo
-- Create date: 15:18 22/04/2016
-- Description: Trigger responsável por manter atualizado o banco de dados do sistema em delphi
--              com o sistema em C#
-- =============================================

use BancoNovo

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select null from sys.objects where name = 'FranquiaTipoConexaoTrigger')
    drop trigger FranquiaTipoConexaoTrigger

go

CREATE TRIGGER FranquiaTipoConexaoTrigger
   ON  FranquiaTipo
   FOR INSERT, DELETE, UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    --
    if exists (select null from deleted) 
    begin
        if exists (select null from inserted)
        begin

            DECLARE tmp CURSOR FAST_FORWARD FOR 
            SELECT [FranquiaTipoId]
                  ,[Descricao]
                  ,[FranquiaTipoMasterId]
                  ,[UsuarioCad]
                  ,[DataHoraCad]
                  ,[Ativo]
            FROM inserted

            declare @FranquiaTipoId integer
            declare @Descricao varchar(100)
            declare @FranquiaTipoMasterId integer
            declare @UsuarioCad varchar(100)
            declare @DataHoraCad datetime
            declare @Ativo bit

            OPEN tmp
            FETCH NEXT FROM tmp
            INTO  @FranquiaTipoId, @Descricao, @FranquiaTipoMasterId, @UsuarioCad, @DataHoraCad, @Ativo

            WHILE @@FETCH_STATUS = 0
            BEGIN   
                print 'update ' + @Descricao

                UPDATE [BANCO_VELHO].[dbo].[FRANQUIA_TIPO]
                   SET 
                       --[CODIGO] = [CODIGO]
                      [DESCRICAO] = @Descricao
                      ,[ID_FRANQUIA_TIPO_MASTER] = @FranquiaTipoMasterId
                      --,[PERCENTUAL_CONSULTOR_FIXO] = <PERCENTUAL_CONSULTOR_FIXO, bit,>
                      --,[PERCENTUAL_SENIOR_FIXO] = <PERCENTUAL_SENIOR_FIXO, bit,>
                      --,[PERCENTUAL_ROYALTIES_SENIOR] = <PERCENTUAL_ROYALTIES_SENIOR, numeric(18,2),>
                      --,[PERCENTUAL_ROYALTIES_CONSULTOR] = <PERCENTUAL_ROYALTIES_CONSULTOR, numeric(18,2),>
                      --,[PERCENTUAL_PUBLICIDADE_SENIOR] = <PERCENTUAL_PUBLICIDADE_SENIOR, numeric(18,2),>
                      --,[PERCENTUAL_PUBLICIDADE_CONSULTOR] = <PERCENTUAL_PUBLICIDADE_CONSULTOR, numeric(18,2),>
                      --,[PERCENTUAL_TAXA_RISCO_SENIOR] = <PERCENTUAL_TAXA_RISCO_SENIOR, numeric(18,2),>
                      --,[PERCENTUAL_TAXA_RISCO_CONSULTOR] = <PERCENTUAL_TAXA_RISCO_CONSULTOR, numeric(18,2),>
                      ,[DATA_HORA_CADASTRO_ALTERACAO] = @DataHoraCad
                      ,[ATIVO] = @Ativo
                      ,[ID_USUARIO_CADASTRO_ALTERACAO] = (SELECT ID_USUARIO FROM BANCO_VELHO.DBO.USUARIO WHERE USUARIO.LOGIN = @UsuarioCad)
                 WHERE ID_FRANQUIA_TIPO = @FranquiaTipoId


                FETCH NEXT FROM tmp
                INTO  @FranquiaTipoId, @Descricao, @FranquiaTipoMasterId, @UsuarioCad, @DataHoraCad, @Ativo                 
            END

            CLOSE tmp
            DEALLOCATE tmp

        end
        else
        begin
            print 'delete'
            DELETE FROM [BANCO_VELHO].[dbo].[FRANQUIA_TIPO]
            WHERE EXISTS (
                SELECT NULL
                FROM DELETED A
                WHERE [BANCO_VELHO].[dbo].[FRANQUIA_TIPO].[ID_FRANQUIA_TIPO] = A.FranquiaTipoId
            )
        end
    end
    else
    begin
        print 'insert'
        set identity_insert [BANCO_VELHO].[dbo].[FRANQUIA_TIPO] on
        INSERT INTO [BANCO_VELHO].[dbo].[FRANQUIA_TIPO]
                   ([ID_FRANQUIA_TIPO]
                   ,[CODIGO]
                   ,[DESCRICAO]
                   ,[ID_FRANQUIA_TIPO_MASTER]
                   ,[PERCENTUAL_CONSULTOR_FIXO]
                   ,[PERCENTUAL_SENIOR_FIXO]
                   ,[PERCENTUAL_ROYALTIES_SENIOR]
                   ,[PERCENTUAL_ROYALTIES_CONSULTOR]
                   ,[PERCENTUAL_PUBLICIDADE_SENIOR]
                   ,[PERCENTUAL_PUBLICIDADE_CONSULTOR]
                   ,[PERCENTUAL_TAXA_RISCO_SENIOR]
                   ,[PERCENTUAL_TAXA_RISCO_CONSULTOR]
                   ,[DATA_HORA_CADASTRO_ALTERACAO]
                   ,[ATIVO]
                   ,[ID_USUARIO_CADASTRO_ALTERACAO])
            SELECT FranquiaTipoId --[ID_FRANQUIA_TIPO]
                   ,NULL --[CODIGO]
                   ,Descricao --[DESCRICAO]
                   ,FranquiaTipoMasterId --[ID_FRANQUIA_TIPO_MASTER]
                   ,0 --[PERCENTUAL_CONSULTOR_FIXO]
                   ,0 --[PERCENTUAL_SENIOR_FIXO]
                   ,0 --[PERCENTUAL_ROYALTIES_SENIOR]
                   ,0 --[PERCENTUAL_ROYALTIES_CONSULTOR]
                   ,0 --[PERCENTUAL_PUBLICIDADE_SENIOR]
                   ,0 --[PERCENTUAL_PUBLICIDADE_CONSULTOR]
                   ,0 --[PERCENTUAL_TAXA_RISCO_SENIOR]
                   ,0 --[PERCENTUAL_TAXA_RISCO_CONSULTOR]
                   ,DataHoraCad --[DATA_HORA_CADASTRO_ALTERACAO]
                   ,[ATIVO]
                   ,(SELECT ID_USUARIO FROM BANCO_VELHO.DBO.USUARIO WHERE USUARIO.LOGIN = UsuarioCad)--[ID_USUARIO_CADASTRO_ALTERACAO]
            FROM inserted
            set identity_insert [BANCO_VELHO].[dbo].[FRANQUIA_TIPO] OFF
    end


END
GO

For this case I will only upgrade from new system to old.

Answer 1

So I’m thinking of creating a Trigger in the bank that does this job.

It is the only viable alternative, especially considering that the technology in the old system is not as sophisticated as in the new system.

Note that it will not be a Trigger per operation, but two. Assuming you are inserting in the new table, but want to keep a mirror on the old one, the new table will also need to Triggers.

For the new system, try to let the application raise the bank through Migrations, and put the Triggers in those Migrations, thus:

public partial class Exemplo : DbMigration
{
    public override void Up()
    {
        /* Código gerado pelo Scaffolding */
        Sql("CREATE OR REPLACE TRIGGER [nomedatrigger] BEFORE UPDATE ON FranquiaTipo ...");
    }

    public override void Down()
    {
        /* Código gerado pelo Scaffolding */
        Sql("DROP TRIGGER [nomedatrigger]");
    }
}

---

His example of Trigger is interesting, but I would do something simpler:

CREATE TRIGGER FranquiaTipo_BancoVelho_DEL
ON [BancoNovo].[dbo].[FranquiaTipo]
FOR DELETE
AS
BEGIN
    DELETE FROM [BANCO_VELHO].[dbo].[FRANQUIA_TIPO]
    WHERE ID_FRANQUIA_TIPO = DELETED.FranquiaTipoId 
END

---

CREATE TRIGGER FranquiaTipo_BancoVelho_UPD
ON [BancoNovo].[dbo].[FranquiaTipo]
FOR UPDATE
AS
BEGIN
    UPDATE [BANCO_VELHO].[dbo].[FRANQUIA_TIPO]
    SET CODIGO = I./* Coloque aqui a coluna de código */
    FROM INSERTED I
    WHERE [BANCO_VELHO].[dbo].[FRANQUIA_TIPO].ID_FRANQUIA_TIPO = I.FranquiaTipoId
END

One Trigger per operation may seem long-winded, but it works better than a Trigger for all cases.

The rest Triggers are analogous to these.

Answer 2

Better use SQL Server Replication, assuming the two databases are in SQL Server. SQL Server has three types of Replication: Transactional, Merge, and Snapshot. In this case, Transactional could be convenient.

https://msdn.microsoft.com/en-us/library/ms152531(SQL.110). aspx

Using triggers would be very fragile and would have difficulties: how to process errors, Locking, etc.

In any case, it is best not to use Cursors because they are very inefficient. SQL Server works with sets.