Creating Trigger

Question

Creating Trigger

Navigation

#1 by (2 votes)

3

I need to develop a trigger where 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 suffering any changes to the task, the statement table goes blank, but once the task expires for example, from 01-11-17 to 03-11-17, taskID goes to the table instruction, getting that way.

INSTRUCTION TABLE

InstrucaoID     TarefaID
          1       170123

sql-server query trigger

asked by anonymous 31.10.2017 / 13:14

1 answer

Next () in CSV Reader with Python 3 Request field fill in url

score 2 · Answer 1

The trigger below will check any changes in one of these fields (which you entered) and var enter the value of the TaskType field in the Tasks table.

Include a cursor so that if you perform a batch operation, 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 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