none
Trigger Linha RRS feed

  • Pergunta

  • Bom dia !

      Amigos exite alguma maneira de se criar uma trigger para ser executada apenas quando um campo de uma linha for alterada, pois tenho a seguinte situação precisava disparar um e-mail sempre que uma data na tabela fosse alterada eu ja tenho toda a estrutura do e-mail porem queria que me fosse disparado assim que a data fosse alterada.

      E não posso fazer por coluna pois ela sofre alteração em vários campos então precisava que pega-se o campo especifico.

    segunda-feira, 16 de setembro de 2019 14:57

Respostas

  •  
    O envio do e-mail e para ter informação do faturamento da empresa assim finalizarem o dia no sistema 

    Se não é necessário incorporar no corpo do e-mail informações sobre os valores de CODEMP e VLRPRM, informando em qual linha ocorreu a alteração, então o código fica mais simples.

    -- código #2
    CREATE TRIGGER dbo.monitora_VALPRM
      on dbo.IBETPRMEMP
      after UPDATE as
      
    begin
    
    -- encerra processamento se não há linha a processar
    IF not exists (SELECT 1 from INSERTED)
      return;
    
    -- encerra processamento caso a coluna VALPRM não faça parte do comando UPDATE
    IF not UPDATE (VALPRM)
      return;
    
    -- encerra processamento se não houve alteração na coluna VALPRM para CODPRM = 17
    IF not exists (SELECT D.VALPRM as D_VALPRM, I.VALPRM as I_VALPRM
                     from INSERTED as I
                          inner join DELETED as D on D.CODEMP = I.CODEMP
                                                     and D.CODPRM = I.CODPRM
                     where D.CODPRM = 17 
                           and (D.VALPRM <> I.VALPRM
                                or (D.VALPRM is null and I.VALPRM is not null)
                                or (D.VALPRM is not null and I.VALPRM is null)
    )
    ) return; -- monta corpo do e-mail DECLARE @tableHTML NVARCHAR(MAX) ; DECLARE @DATA varchar(50) ; DECLARE @DATA1 varchar(50) ; DECLARE @assunto varchar(50) ; set @DATA = CONVERT (char, GETDATE()-3,103) ; set @DATA1 = CONVERT (date, GETDATE()-3); set @assunto= 'Fechamento Caixa '+@DATA SET @tableHTML = N'<font color ="Red"><H1>Extrato Caixa</H1></font>' + N'<font color ="Red"><H2>Fechamento dia</H2></font>' + N'<font color ="blue">'+@DATA+ '</font>'+ N'<table border="1">' + N'<tr><th>TIPO</th><th>VALOR</th>' + CAST( (SELECT TD= C1 ,'', TD= C2,'' from ( SELECT IBETTPOPRSCTA.DESTPOPRSCTA AS c1, Flexx00151700.dbo.func_converter_moeda ( SUM (IBETDTTPRSCTA.VALDTTPRSCTA),'pt-br') AS c2 FROM Flexx00151700.dbo.IBETDTTPRSCTA IBETDTTPRSCTA (Nolock), Flexx00151700.dbo.IBETTPOPRSCTA IBETTPOPRSCTA (Nolock), Flexx00151700.dbo.IBETVIAVEC IBETVIAVEC (Nolock) WHERE IBETTPOPRSCTA.CODEMP = IBETDTTPRSCTA.CODEMP AND IBETTPOPRSCTA.CODTPOPRSCTA = IBETDTTPRSCTA.CODTPOPRSCTA AND IBETTPOPRSCTA.TPOPRSCTA = IBETDTTPRSCTA.TPOPRSCTA AND IBETVIAVEC.CODEMP = IBETDTTPRSCTA.CODEMP AND IBETVIAVEC.CODEMP = IBETTPOPRSCTA.CODEMP AND IBETVIAVEC.CODVEC = IBETDTTPRSCTA.CODVEC AND IBETVIAVEC.NUMVIAVEC = IBETDTTPRSCTA.NUMVIAVEC AND CONVERT (date, IBETVIAVEC.DATRTNVIAVEC) = @DATA1 and (IBETDTTPRSCTA.TPOPRSCTA<>'D') GROUP BY IBETTPOPRSCTA.DESTPOPRSCTA, IBETDTTPRSCTA.TPOPRSCTA ) as TD order by 1 FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' ; -- enfileira e-mail EXEC msdb.dbo.sp_send_dbmail @profile_name = 'servidor teste', @recipients='handersonpais@gmail.com', @subject = @assunto, @body = @tableHTML, @body_format = 'HTML' ; end; go


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.
    • Marcado como Resposta HANDERSONPAIS terça-feira, 17 de setembro de 2019 11:32
    • Editado José Diz terça-feira, 17 de setembro de 2019 11:45
    segunda-feira, 16 de setembro de 2019 20:05

Todas as Respostas

  • Handerson,

    • qual é o nome da tabela e as colunas que fazem parte da chave primária?
    • a coluna a ser monitorada é a VALPRM?
    • somente para CODPRM 17?

     

    A forma como o SQL Server manipula os eventos que acionam gatilhos (trigger) pode se transformar em verdadeira armadilha para quem programa o procedimento de gatilho e desconhece os detalhes específicos do SQL Server, pois enquanto vários gerenciadores de banco de dados acionam o gatilho uma vez para cada linha (for each row), no SQL Server o gatilho é acionado uma vez para cada instrução DML (DELETE, INSERT, UPDATE). Sugiro a leitura do artigo “Armadilhas na programação de trigger” para conhecer algumas das armadilhas.

     

    Eis esboço do código do procedimento de gatilho:

    -- código #1 v4 CREATE TRIGGER monitora_VALPRM on tabela after UPDATE as begin -- encerra processamento se não há linha a processar IF not exists (SELECT 1 from INSERTED) return; -- encerra processamento caso a coluna VALPRM não faça parte do comando UPDATE IF not UPDATE (VALPRM) return; -- analisa linha a linha que tenha sido alterada na coluna VALPRM declare @I_CODEMP ___, @I_CODPRM ___, @D_VALPRM ___, @I_VALPRM ___;
    declare @Houve_caso bit;
    declare Le_Linha cursor for SELECT I.CODEMP, I.CODPRM, D.VALPRM as D_VALPRM, I.VALPRM as I_VALPRM from INSERTED as I inner join DELETED as D on D.CODEMP = I.CODEMP and D.CODPRM = I.CODPRM
    where I.CODPRM = 17
    and (D.VALPRM <> I.VALPRM
    or (D.VALPRM is null and I.VALPRM is not null)               or (D.VALPRM is not null and I.VALPRM is null));
    set @Houve_caso= 'false';
    OPEN Le_Linha; -- lê primeira linha que tenha sido alterada FETCH NEXT from Le_Linha into @I_CODEMP, @I_CODPRM, @D_VALPRM, @I_VALPRM; while @@fetch_status = 0 begin
    set @Houve_caso= 'true';
    -- monta corpo do e-mail ... acrescentar informações a constar no corpo do e-mail -- lê próxima linha que tenha sido alterada FETCH NEXT from Le_Linha into @I_CODEMP, @I_CODPRM, @D_VALPRM, @I_VALPRM; end;

    CLOSE Le_Linha;
    DEALLOCATE Le_Linha;
    -- enfileira e-mail
    IF @Houve_caso = 'true' begin
    ... acrescentar aqui o código de envio do e-mail
    end; end; go

    Não testei, pode conter erro(s).

    O código acima considera que a chave primária da tabela é composta pelas colunas CODEMP e CODPRM.

     

    Lembre-se de marcar esta resposta se ela te ajudou a resolver o problema.


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz segunda-feira, 16 de setembro de 2019 18:38
    segunda-feira, 16 de setembro de 2019 15:47
  • E deu erro não deixa updatar 

    Msg 16915, Level 16, State 1, Procedure Monitora_Fechamento_dia, Line 20
    A cursor with the name 'Le_Linha' already exists.
    The statement has been terminated.

    segunda-feira, 16 de setembro de 2019 16:45
  • E deu erro não deixa updatar 

    Msg 16915, Level 16, State 1, Procedure Monitora_Fechamento_dia, Line 20
    A cursor with the name 'Le_Linha' already exists.
    The statement has been terminated.

    Handerson, esse erro provavelmente ocorreu ao executar o código pela segunda vez.

    Acrescente

         CLOSE Le_Linha;
         DEALLOCATE Le_Linha;

    imediatamente antes de

         -- enfileira e-mail
         IF @Houve_caso = 'true'


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    segunda-feira, 16 de setembro de 2019 18:39
  • Deu o mesmo erro
    segunda-feira, 16 de setembro de 2019 18:54
  • Deu o mesmo erro

    Para ter certeza de que não há cursor em aberto, execute à parte:

        CLOSE Le_Linha;
        DEALLOCATE Le_Linha;

    na mesma sessão em que está o código.

    O que ocorreu é que na primeira execução o cursor ficou aberto.

    O código #1 é para funcionar dentro de procedimento de gatilho, pois envolve o uso das tabelas virtuais INSERTED e DELETED.


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz segunda-feira, 16 de setembro de 2019 18:59
    segunda-feira, 16 de setembro de 2019 18:56
  • Mesmo fechando deu o erro

    CREATE TRIGGER monitora_VALPRM
      on DBO.IBETPRMEMP
      after UPDATE as
      
    begin

    -- encerra processamento se não há linha a processar
    IF not exists (SELECT 1 from INSERTED)
      return;

    -- encerra processamento caso a coluna VALPRM não faça parte do comando UPDATE
    IF not UPDATE (VALPRM)
      return;

    -- analisa linha a linha que tenha sido alterada na coluna VALPRM
    declare @I_CODEMP int, @I_CODPRM int, @D_VALPRM varchar(50), @I_VALPRM varchar(50);
    declare @Houve_caso bit;

    declare Le_Linha cursor
      for SELECT I.CODEMP, I.CODPRM, D.VALPRM as D_VALPRM, I.VALPRM as I_VALPRM
            from INSERTED as I
                 inner join DELETED as D on D.CODEMP = I.CODEMP
                                            and D.CODPRM = I.CODPRM
            where I.CODPRM = 17 
                  and (D.VALPRM <> I.VALPRM
                       or (D.VALPRM is null and I.VALPRM is not null)
                       or (D.VALPRM is not null and I.VALPRM is null));

    set @Houve_caso= 'false';
    OPEN Le_Linha;

    -- lê primeira linha que tenha sido alterada
    FETCH NEXT 
      from Le_Linha
      into @I_CODEMP, @I_CODPRM, @D_VALPRM, @I_VALPRM;

    while @@fetch_status = 0
      begin
      set @Houve_caso= 'true';

      -- monta corpo do e-mail
     DECLARE @tableHTML  NVARCHAR(MAX) ;


    DECLARE @DATA  varchar(50) ;
    DECLARE @DATA1  varchar(50) ;
    DECLARE @assunto  varchar(50) ;
    set  @DATA = CONVERT (char, GETDATE()-3,103) ;
    set  @DATA1 = CONVERT (date, GETDATE()-3);
    set @assunto= 'Fechamento Caixa '+@DATA

    SET @tableHTML =
        N'<font color ="Red"><H1>Extrato Caixa</H1></font>' +
     N'<font color ="Red"><H2>Fechamento dia</H2></font>' +
         N'<font color ="blue">'+@DATA+ '</font>'+
        N'<table border="1">' +
        N'<tr><th>TIPO</th><th>VALOR</th>' +
        
        CAST(
             (SELECT
               TD= C1 ,'',
               TD= C2,''
               from (                      

    SELECT IBETTPOPRSCTA.DESTPOPRSCTA AS c1, Flexx00151700.dbo.func_converter_moeda ( SUM (IBETDTTPRSCTA.VALDTTPRSCTA),'pt-br') AS c2
    FROM Flexx00151700.dbo.IBETDTTPRSCTA IBETDTTPRSCTA (Nolock), 
    Flexx00151700.dbo.IBETTPOPRSCTA IBETTPOPRSCTA (Nolock), 
    Flexx00151700.dbo.IBETVIAVEC IBETVIAVEC (Nolock)
    WHERE IBETTPOPRSCTA.CODEMP = IBETDTTPRSCTA.CODEMP AND 
    IBETTPOPRSCTA.CODTPOPRSCTA = IBETDTTPRSCTA.CODTPOPRSCTA AND
     IBETTPOPRSCTA.TPOPRSCTA = IBETDTTPRSCTA.TPOPRSCTA AND 
     IBETVIAVEC.CODEMP = IBETDTTPRSCTA.CODEMP AND 
     IBETVIAVEC.CODEMP = IBETTPOPRSCTA.CODEMP AND 
     IBETVIAVEC.CODVEC = IBETDTTPRSCTA.CODVEC AND 
     IBETVIAVEC.NUMVIAVEC = IBETDTTPRSCTA.NUMVIAVEC AND 
      CONVERT (date, IBETVIAVEC.DATRTNVIAVEC) = @DATA1 
      and (IBETDTTPRSCTA.TPOPRSCTA<>'D') 
     
    GROUP BY IBETTPOPRSCTA.DESTPOPRSCTA, IBETDTTPRSCTA.TPOPRSCTA
     

    )                              
                     as TD order by 1
                    FOR XML PATH('tr'), TYPE 
             ) AS NVARCHAR(MAX) ) +   N'</table>' ;

      -- lê próxima linha que tenha sido alterada
      FETCH NEXT 
        from Le_Linha
        into @I_CODEMP, @I_CODPRM, @D_VALPRM, @I_VALPRM;
      end;

    CLOSE Le_Linha;
    DEALLOCATE Le_Linha;

    -- enfileira e-mail

    IF @Houve_caso = 'true'
      begin
      
    EXEC msdb.dbo.sp_send_dbmail 
         @profile_name = 'servidor teste',
        @recipients='handersonpais@gmail.com',
        @subject = @assunto,
        @body = @tableHTML,
        @body_format = 'HTML' ;
        
      end;

    end;
    go

    segunda-feira, 16 de setembro de 2019 19:21
  • Mesmo fechando deu o erro

    Provavelmente algum erro está acontecendo dentro do procedimento que está deixando o cursor aberto. A declaração de variáveis dentro do laço é um deles, caso o laço seja executado mais de uma vez.

    Antes de continuar, uma dúvida sobre o trecho "precisava disparar um e-mail sempre que uma data na tabela fosse alterada": não é necessário incorporar no corpo do e-mail informações sobre os valores de CODEMP e VLRPRM, informando em qual linha ocorreu a alteração?

    Sempre que uma data for alterada, deve-se enviar um único e-mail ou envia-se um para cada caso alterado?


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz segunda-feira, 16 de setembro de 2019 20:14
    segunda-feira, 16 de setembro de 2019 19:44
  • O envio do e-mail e para ter informação do faturamento da empresa assim finalizarem o dia no sistema 
    segunda-feira, 16 de setembro de 2019 19:47
  •  
    O envio do e-mail e para ter informação do faturamento da empresa assim finalizarem o dia no sistema 

    Se não é necessário incorporar no corpo do e-mail informações sobre os valores de CODEMP e VLRPRM, informando em qual linha ocorreu a alteração, então o código fica mais simples.

    -- código #2
    CREATE TRIGGER dbo.monitora_VALPRM
      on dbo.IBETPRMEMP
      after UPDATE as
      
    begin
    
    -- encerra processamento se não há linha a processar
    IF not exists (SELECT 1 from INSERTED)
      return;
    
    -- encerra processamento caso a coluna VALPRM não faça parte do comando UPDATE
    IF not UPDATE (VALPRM)
      return;
    
    -- encerra processamento se não houve alteração na coluna VALPRM para CODPRM = 17
    IF not exists (SELECT D.VALPRM as D_VALPRM, I.VALPRM as I_VALPRM
                     from INSERTED as I
                          inner join DELETED as D on D.CODEMP = I.CODEMP
                                                     and D.CODPRM = I.CODPRM
                     where D.CODPRM = 17 
                           and (D.VALPRM <> I.VALPRM
                                or (D.VALPRM is null and I.VALPRM is not null)
                                or (D.VALPRM is not null and I.VALPRM is null)
    )
    ) return; -- monta corpo do e-mail DECLARE @tableHTML NVARCHAR(MAX) ; DECLARE @DATA varchar(50) ; DECLARE @DATA1 varchar(50) ; DECLARE @assunto varchar(50) ; set @DATA = CONVERT (char, GETDATE()-3,103) ; set @DATA1 = CONVERT (date, GETDATE()-3); set @assunto= 'Fechamento Caixa '+@DATA SET @tableHTML = N'<font color ="Red"><H1>Extrato Caixa</H1></font>' + N'<font color ="Red"><H2>Fechamento dia</H2></font>' + N'<font color ="blue">'+@DATA+ '</font>'+ N'<table border="1">' + N'<tr><th>TIPO</th><th>VALOR</th>' + CAST( (SELECT TD= C1 ,'', TD= C2,'' from ( SELECT IBETTPOPRSCTA.DESTPOPRSCTA AS c1, Flexx00151700.dbo.func_converter_moeda ( SUM (IBETDTTPRSCTA.VALDTTPRSCTA),'pt-br') AS c2 FROM Flexx00151700.dbo.IBETDTTPRSCTA IBETDTTPRSCTA (Nolock), Flexx00151700.dbo.IBETTPOPRSCTA IBETTPOPRSCTA (Nolock), Flexx00151700.dbo.IBETVIAVEC IBETVIAVEC (Nolock) WHERE IBETTPOPRSCTA.CODEMP = IBETDTTPRSCTA.CODEMP AND IBETTPOPRSCTA.CODTPOPRSCTA = IBETDTTPRSCTA.CODTPOPRSCTA AND IBETTPOPRSCTA.TPOPRSCTA = IBETDTTPRSCTA.TPOPRSCTA AND IBETVIAVEC.CODEMP = IBETDTTPRSCTA.CODEMP AND IBETVIAVEC.CODEMP = IBETTPOPRSCTA.CODEMP AND IBETVIAVEC.CODVEC = IBETDTTPRSCTA.CODVEC AND IBETVIAVEC.NUMVIAVEC = IBETDTTPRSCTA.NUMVIAVEC AND CONVERT (date, IBETVIAVEC.DATRTNVIAVEC) = @DATA1 and (IBETDTTPRSCTA.TPOPRSCTA<>'D') GROUP BY IBETTPOPRSCTA.DESTPOPRSCTA, IBETDTTPRSCTA.TPOPRSCTA ) as TD order by 1 FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' ; -- enfileira e-mail EXEC msdb.dbo.sp_send_dbmail @profile_name = 'servidor teste', @recipients='handersonpais@gmail.com', @subject = @assunto, @body = @tableHTML, @body_format = 'HTML' ; end; go


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.
    • Marcado como Resposta HANDERSONPAIS terça-feira, 17 de setembro de 2019 11:32
    • Editado José Diz terça-feira, 17 de setembro de 2019 11:45
    segunda-feira, 16 de setembro de 2019 20:05
  • Obrigado meu amigo era isso mesmo que precisava agradeço muito sua atenção.
    terça-feira, 17 de setembro de 2019 11:32
  • Obrigado meu amigo era isso mesmo que precisava agradeço muito sua atenção.
    Handerson, somente agora percebi que havia trechos repetidos na resposta que postei anteriormente, o que deve ter dificultado a compreensão da solução proposta. Já limpei a resposta.

    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz quarta-feira, 18 de setembro de 2019 09:52
    terça-feira, 17 de setembro de 2019 11:46