Creation of Trigger

Question

I need to develop a Rigger in which any change that occurs in one of these fields below the Task table should send the task information to the Instruction table.

TASK TABLE

TarefaID    Vencimento  Status      Caminho     ModuloID
  170123    2017-11-01       0    Desativar           71

INSTRUCTION TABLE

InstrucaoID     TarefaID

In short. Without undergoing any change in the task, the instruction table is empty, but from the moment that for example, the task expiration is changed, from 01-11-17 to 03-11-17, the task goes to the instruction table, thus remaining.

INSTRUCTION TABLE

InstrucaoID     TarefaID
          1       170123

Answer 1

Trigger below will verify any change in one of these fields (which you have entered) and var enter the value of the Tarefaid field in the Tasks table.

Includes a cursor so that if a batch operation is performed, all occurrences are recorded in the Task table.

If any exception occurs, it will be captured and displayed to the user in a custom way.

Note: I assumed that your Tasks table has the field InstrucaoID as identity;

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TGR_TAREFA_AU]') AND type in (N'TR'))
    DROP TRIGGER [dbo].[TGR_TAREFA_AU]

GO

CREATE TRIGGER [dbo].[TGR_TAREFA_AU]
ON [dbo].[TAREFA]
WITH ENCRYPTION
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @tTarefaID INT

    IF UPDATE(TarefaID) OR UPDATE(Vencimento) OR UPDATE(Status) OR UPDATE(Caminho) OR UPDATE(ModuloID)
    BEGIN
        BEGIN TRAN

        BEGIN TRY
            DECLARE CURSOR_TAREFA CURSOR FOR 
            SELECT TarefaID FROM Inserted INS

            OPEN CURSOR_TAREFA

            FETCH NEXT FROM CURSOR_TAREFA INTO @tTarefaID
            WHILE @@FETCH_STATUS = 0
            BEGIN
                INSERT INTO INSTRUCAO (TarefaID) VALUES(@tTarefaID)

                FETCH NEXT FROM CURSOR_TAREFA INTO @tTarefaID
            END

            CLOSE CURSOR_TAREFA
            DEALLOCATE CURSOR_TAREFA
            COMMIT
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0 ROLLBACK

            DECLARE @_ERROR_PROCEDURE VARCHAR(MAX)
            SET @_ERROR_PROCEDURE = 'Err Nº: ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) +' | Severidade: '+ CAST(ERROR_SEVERITY() AS VARCHAR(10)) + ' | Estado: ' + CAST(ERROR_STATE() AS VARCHAR(10)) + ' | Procedure: '+ ISNULL(ERROR_PROCEDURE(),'') + ' | Linha: '+ CAST(ERROR_LINE() AS VARCHAR(10)) + ' | Erro: '+ ERROR_MESSAGE()   

            RAISERROR(@_ERROR_PROCEDURE,16,1)           
        END CATCH
    END
END
GO