Trigger without replicating data - SQL Server 2012

Question

Trigger without replicating data - SQL Server 2012

Navigation

#1 by (0 votes)

4

I have a trigger in the FRM_46 table that would be to replicate the data right after the insert or update for the FRM_31 table, however, it is not replicating, I already analyzed and did the tests, but I could not verify where the error is .

ALTER TRIGGER [dbo].[TGR_FORMULARIO_REPLICAÇÃO_NOVO_PROCESSO_ESTRATEGICO]
   ON [dbo].[FRM_46] 
    AFTER UPDATE, INSERT
AS 
BEGIN
    DECLARE
    @TarefaID  INT,
    @DataDoPerfilCliente nVARCHAR(15),
    @PerfilCliente nVARCHAR(15),
    @ContaID INT


    BEGIN
        SELECT  @TarefaID = F.TarefaID,
                @DataDoPerfilCliente = CONVERT(DATE,GETDATE(),103),
                @ContaID = t.UsuIDCliente,
                @PerfilCliente = f.C33
        FROM inserted F
        INNER JOIN Tarefa T on t.TarID = f.ChamadoID

        IF @ContaID NOT IN (SELECT ContaID FROM FRM_31)
            INSERT INTO FRM_31 (ContaID, C01, C02, C05) VALUES (@ContaID, @DataDoPerfilCliente, @PerfilCliente, @TarefaID);
        IF @ContaID IN (SELECT ContaID FROM FRM_31)
            UPDATE FRM_31 SET C01 = @DataDoPerfilCliente, C02 = @PerfilCliente, C05 = @TarefaID, C08 = null WHERE ContaID = @ContaID
    END
END

sql-server query trigger

asked by anonymous 13.11.2017 / 19:28

1 answer

Identify the name of the streets contained in a polygon play button plays another audio

score 0 · Accepted Answer

The displayed%% will only run once, regardless of the amount of records entered or changed. To change thinking of multiple records I suggest the following:

ALTER TRIGGER dbo.tgr_formulario_replicação_novo_processo_estrategico
   ON dbo.frm_46
AFTER UPDATE, INSERT
AS 
BEGIN
  SET NOCOUNT ON;

  MERGE frm_31 AS destino
  USING (SELECT f.tarefaid,
                CAST(GETDATE() AS DATE) AS data,
                t.usuidcliente,
                f.c33
          FROM inserted f
               INNER JOIN tarefa t ON t.tarid = f.chamadoid) AS origem
     ON (destino.contaid = origem.usuidcliente)
  -- Quando encontrar um registro que corresponda, apenas atualiza com os valores que constam aqui
  WHEN MATCHED THEN
    UPDATE
       SET destino.c01 = origem.data,
           destino.c02 = origem.c33,
           destino.c05 = origem.tarefaid,
           destino.c08 = null
  -- Quando não encontrar irá inserir
  WHEN NOT MATCHED BY TARGET THEN
    INSERT (contaid,
            c01,
            c02,
            c05)
    VALUES (origem.usuidcliente,
            origem.data,
            origem.c33,
            origem.tarefaid);
END;
GO

In the code shown above, we checked for a record in the destination table with the same TRIGGER . If it exists, ContaID is executed by updating the existing record. If it does not exist in the target table ( MATCHED ), the FRM_31 case is executed by inserting a new record.

Note also that you are only using the NOT MATCHED BY TARGET column as the key, so if there is a new record, but with an existing ContaID , the row in the ContaID table will only be updated.

FRM_31

Performs insert, update, or delete operations on a target table based on the results of joining with the source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in a table based on the differences found in the other table.