sql-server – Trigger Creation

Question:

I need to develop a trigger in which any change that occurs in one of these fields below the Task table must 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

Summing up. Without undergoing any change in the task, the instruction table is blank, but from the moment, for example, when the task's expiration date is changed, from 11-01-17 to 11-03-17, the taskID goes to the table instruction, getting that way.

INSTRUCTION TABLE

InstrucaoID     TarefaID
          1       170123

Answer:

The trigger below will check any changes in one of these fields (which you have informed) and insert the value of the TaskID 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 exceptions occur, they will be caught and displayed to the user in a personalized way.

Note: I InstrucaoID that your Tasks table has the InstrucaoID field 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
Scroll to Top