Trigger Linha

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

Deu o mesmo erro

Answer 5

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.

Answer 6

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='[e-mail]',
    @subject = @assunto,
    @body = @tableHTML,
    @body_format = 'HTML' ;
    
  end;

end;
go

Answer 7

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.

Answer 8

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

Answer 9

 

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='[e-mail]',
    @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.

Answer 10

Obrigado meu amigo era isso mesmo que precisava agradeço muito sua atenção.

Answer 11

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.