Keep records of synchronized tables between two different databases

Question

Keep records of synchronized tables between two different databases

Navigation

#1 by (4 votes)
#2 by (0 votes)

5

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

So, you would have to keep the following tables up to date

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 work.

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

Based on the idea of creating Trigger , I got the following 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 the new system to the old one.

sql-server asp.net entity-framework asp.net-mvc

asked by anonymous 22.04.2016 / 21:22

2 answers

0

Best to use SQL Server Replication, assuming both databases are in SQL Server. SQL Server has three types of replication: Transactional, Merge, and Snapshot. In this case, Transactional might be convenient.

link

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

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

27.04.2016 / 13:02

How long is the data allocated to functions? Input or raw_input?

score 4 · Accepted Answer

So I'm thinking of creating a Trigger on the bank that does this work.

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

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

For the new system, try to let the application upload the database through Migrations , and place the Triggers on these Migrations , like this: / p>

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]");
    }
}

Your Trigger example 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

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

The other Triggers are analogous to these.