Trigger without replicating data - SQL Server 2012

Question

Guys, I have a Trigger in the FRM_46 table that would replicate the data right after the Insert or update to the FRM_31 table, however, it is not replicating, I already analyzed and made the tests, however, 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

Answer 1

To TRIGGER demonstrated will only run once, regardless of the amount of records entered or changed. To change thinking of several 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 verify the existence of a record in the target table with the same ContaID. If any, the case MATCHED is executed by updating the existing record. If it does not exist in the target table (FRM_31), the case NOT MATCHED BY TARGET is executed by inserting a new record.

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

---

MERGE

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