none
Trigger para enviar email quando é realizado update RRS feed

  • Pergunta

  • Boa tarde,

    Tenho uma tabela tb_planos_acoes , quero enviar um email todas as vezes que o valor da coluna status_fim for alterado para encerrado, estou com dificuldades em criar a trigger.

    Obs.: já tenho o dbemail configurado.

    segunda-feira, 20 de maio de 2019 16:58

Respostas

  • Deleted
    • Marcado como Resposta Edvaldo A segunda-feira, 20 de maio de 2019 19:49
    segunda-feira, 20 de maio de 2019 18:22
  • Edvaldo,

    Certo, podemos completar o código colocando uma condição:

    alter TRIGGER TestMyTable_Upd
    ON dbo.tb_planos_acoes
    AFTER UPDATE
    AS
    BEGIN
    Declare @Codigo Int
    
    Select @Codigo = Codigo From Inserted
    Where Status_Fim = 'Encerrado'
    
    Update TB_Planos_Acoes
    Set DT_Trigger = GetDate()
    Where Codigo = @Codigo
    And Status_Fim = 'Encerrado'
    
    
    If (Select Status_Fim From Inserted Where Código = @Codigo) = 'Encerrado'
     Execute P_EnviarEmail 
    Else
     Return
    end;
    
    
    ------- PROCEDURE -----------
    alter Procedure P_EnviarEmail
    As
    Begin
    exec msdb.dbo.sp_send_dbmail 
    @profile_name = 'MailTest6', 
    @recipients = 'meuemail@dominio.com.br', 
    @subject = 'Testando envio automatico do SQL Server', 
    @body = 'Funcionou o envio do e-mail', 
    @body_format = 'text'
    
    end;
    Veja se isso te ajuda.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]


    segunda-feira, 20 de maio de 2019 18:50
    Moderador

Todas as Respostas

  • Segue o script que criei, porém para qualquer update ele envia email, quero que ele envie e-mail somente quando a coluna status_fim for alterada para encerrado

    alter TRIGGER TestMyTable_Upd
    ON dbo.tb_planos_acoes
    AFTER UPDATE
    AS
    BEGIN
        IF @@ROWCOUNT = 0 RETURN;
        SET NOCOUNT ON;
    update tb_planos_acoes set dt_trigger=getdate()
    from tb_planos_acoes t
      inner join inserted i on t.codigo=i.codigo
      and i.status_fim='encerrado';
    exec msdb.dbo.sp_send_dbmail 
     @profile_name = 'MailTest6', 
     @recipients = 'meuemail@dominio.com.br', 
     @subject = 'Testando envio automatico do SQL Server', 
     @body = 'Funcionou o envio do e-mail', 
     @body_format = 'text'
    end;

    segunda-feira, 20 de maio de 2019 18:10
  • Boa tarde.

    Edvaldo, não sei se compreendi corretamente mas sua trigger está realizando o UPDATE e em sequida envia o e-mail.

    O correto não seria sua trigger fazer um SELECT (no lugar do update) e, somente após localizar um registro com status_fim = encerrado, realizar o disparo do e-mail?

    Att,

    Otavio Wollny

    segunda-feira, 20 de maio de 2019 18:15
  • Deleted
    • Marcado como Resposta Edvaldo A segunda-feira, 20 de maio de 2019 19:49
    segunda-feira, 20 de maio de 2019 18:22

  • O correto não seria sua trigger fazer um SELECT (no lugar do update) e, somente após localizar um registro com status_fim = encerrado, realizar o disparo do e-mail?

    Att,

    Otavio Wollny

    Sim, verdade Otavio,


    segunda-feira, 20 de maio de 2019 18:26
  • Edvaldo, haveria possibilidade de que a própria aplicação enviasse o e-mail, ao alterar o valor da coluna status_fim, evitando assim o uso do procedimento de gatilho (trigger)?

    Quais colunas da tabela tb_planos_acoes cujo conteúdo terá que ser enviado pelo e-mail?

    .

    1 - Não é possível via aplicação.

    2 - Os campos que vou enviar por email são : codigo, designacao, cliente e status_fim

    segunda-feira, 20 de maio de 2019 18:29
  • Edvaldo,

    Pois bem, vamos por partes:

    1 - Eu não utilizaria o comando IF com a variável de sistema @@RowCount para identificar a ocorrência de linhas processadas, na verdade o trigger é um comando transacional, e mesmo vai ocorrer caso os eventos informados nele sejam realizados, você pode fazer uso do IF para analisar se as linhas foram atualizadas, ou se ocorreu um Update em determinada coluna.

    2 - O uso do Set NoCount On, sim, isso é importante, e não em sentido tanto um trigger como uma Stored Procedure retornar a quantidade de linhas processadas, a não ser que este número tenha importância para algum processo da sua aplicação.

    3 - Aparentemente o comando Update esta correto, inicialmente como não conheço sua regra de negócio, o que me chamou a atenção é o uso do Inner Join, mas acredito que aqui esteja o primeiro ponto de atenção, talvez poderíamos pensar em algo desta forma:

    Declare @Codigo Int Select @Codigo = Codigo From Inserted Where Status_Fim = 'Encerrado' Update TB_Planos_Acoes Set DT_Trigger = GetDate() Where Codigo = @Codigo And Status_Fim = 'Encerrado'

    Execute P_EnviarEmail -- veja a minha observação abaixo, no passo 4.

    4 - Logo após a execução do Update, eu não declararia a chamada da stored procedure sp_send_dbmail, na verdade, eu criaria uma outra stored procedure contendo somente o código para enviar o e-mail, conforme o exemplo abaixo:

    Create Procedure P_EnviarEmail
    As
    Begin
    exec msdb.dbo.sp_send_dbmail 
         @profile_name = 'MailTest6', 
         @recipients = 'meuemail@dominio.com.br', 
         @subject = 'Testando envio automatico do SQL Server', 
         @body = 'Funcionou o envio do e-mail', 
         @body_format = 'text'
    End

    Como o trigger é um comando transacional, e ele fica aninhado aos eventos executados na tabela ao qual o mesmo esta vinculado, eu particularmente falando prefiro sempre que for fazer uso de uma stored procedure em conjunto com o trigger, chamar a stored procedure dentro do trigger mas a mesma sendo executada em outra sessão, para que justamente não ocorra algum tipo de bloqueio da sessão do trigger, por isso declarei o comando Exec.

    Bom, esta foi uma análise inicial, vamos evoluindo, e claro, aqui apresentei a maneira que consegui entender a sua necessidade, o que pode ser aplicada e analisada de outras maneiras.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]


    segunda-feira, 20 de maio de 2019 18:30
    Moderador
  • Edvaldo, haveria possibilidade de que a própria aplicação enviasse o e-mail, ao alterar o valor da coluna status_fim, evitando assim o uso do procedimento de gatilho (trigger)?

    Quais colunas da tabela tb_planos_acoes cujo conteúdo terá que ser enviado pelo e-mail?

    Eis esboço do código:

    -- código #1
    CREATE TRIGGER nometrigger
      on tb_planos_acoes
      after UPDATE as
    
    begin
    -- encerra se não há algo para processar
    IF not exists (SELECT * from INSERTED)
      return;
    
    -- encerra se coluna status_fim não faz parte de UPDATE SET
    IF not update (status_fim)
      return;
    
    -- cria tabela temporária com os casos selecionados para envio de e-mail
    declare @NL int;
    declare @Enviar (I tinyint identity, colunas);
    INSERT into @Enviar (colunas)
      SELECT colunas
        from DELETED as D
             inner join INSERTED as I on ___
        where D.status_fim <> 'encerrado' 
              and I.status_fim = 'encerrado';
    
    set @NL= @@rowcount;
    IF @NL = 0
      return;
    
    --
    declare @I int, @colunas ...;
    set @I= 1;
    while @I <= @NL
      begin
      SELECT @colunas= colunas
        from @Enviar
        where I = @I;
      EXECUTE sp_send_dbmail ...
      --
      set @I+= 1;
      end;
    end; go

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

    O código está preparado para tratar casos em que há alteração em várias linhas em uma única instrução UPDATE. A tabela @Enviar seleciona os casos em que há a alteração de status para "encerrado".

    Você precisa complementar o código, informando as colunas da tabela tb_plano_acoes que serão utilizadas para envio do e-mail.


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


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



    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]


    segunda-feira, 20 de maio de 2019 18:40
    Moderador
  • Edvaldo,

    Pois bem, vamos por partes:

    1 - Eu não utilizaria o comando IF com a variável de sistema @@RowCount para identificar a ocorrência de linhas processadas, na verdade o trigger é um comando transacional, e mesmo vai ocorrer caso os eventos informados nele sejam realizados, você pode fazer uso do IF para analisar se as linhas foram atualizadas, ou se ocorreu um Update em determinada coluna.

    2 - O uso do Set NoCount On, sim, isso é importante, e não em sentido tanto um trigger como uma Stored Procedure retornar a quantidade de linhas processadas, a não ser que este número tenha importância para algum processo da sua aplicação.

    3 - Aparentemente o comando Update esta correto, inicialmente como não conheço sua regra de negócio, o que me chamou a atenção é o uso do Inner Join, mas acredito que aqui esteja o primeiro ponto de atenção, talvez poderíamos pensar em algo desta forma:

    Declare @Codigo Int Select @Codigo = Codigo From Inserted Where Status_Fim = 'Encerrado' Update TB_Planos_Acoes Set DT_Trigger = GetDate() Where Codigo = @Codigo And Status_Fim = 'Encerrado'

    Execute P_EnviarEmail -- veja a minha observação abaixo, no passo 4.

    4 - Logo após a execução do Update, eu não declararia a chamada da stored procedure sp_send_dbmail, na verdade, eu criaria uma outra stored procedure contendo somente o código para enviar o e-mail, conforme o exemplo abaixo:

    Create Procedure P_EnviarEmail
    As
    Begin
    exec msdb.dbo.sp_send_dbmail 
         @profile_name = 'MailTest6', 
         @recipients = 'meuemail@dominio.com.br', 
         @subject = 'Testando envio automatico do SQL Server', 
         @body = 'Funcionou o envio do e-mail', 
         @body_format = 'text'
    End

    Como o trigger é um comando transacional, e ele fica aninhado aos eventos executados na tabela ao qual o mesmo esta vinculado, eu particularmente falando prefiro sempre que for fazer uso de uma stored procedure em conjunto com o trigger, chamar a stored procedure dentro do trigger mas a mesma sendo executada em outra sessão, para que justamente não ocorra algum tipo de bloqueio da sessão do trigger, por isso declarei o comando Exec.

    Bom, esta foi uma análise inicial, vamos evoluindo, e claro, aqui apresentei a maneira que consegui entender a sua necessidade, o que pode ser aplicada e analisada de outras maneiras.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]


    Junior,

    Segui sua sugestão, porém qualquer update na coluna status_fim ele está disparando e-mail, eu quero que somente quando a coluna status_fim seja atualizada para encerrado 

    -----------------  TRIGGER  ------------------------
    alter TRIGGER TestMyTable_Upd
    ON dbo.tb_planos_acoes
    AFTER UPDATE
    AS
    BEGIN
    Declare @Codigo Int
    
    Select @Codigo = Codigo From Inserted
    Where Status_Fim = 'Encerrado'
    
    Update TB_Planos_Acoes
    Set DT_Trigger = GetDate()
    Where Codigo = @Codigo
    And Status_Fim = 'Encerrado'
    Execute P_EnviarEmail 
    end;
    
    
    ------- PROCEDURE -----------
    alter Procedure P_EnviarEmail
    As
    Begin
    exec msdb.dbo.sp_send_dbmail 
    @profile_name = 'MailTest6', 
    @recipients = 'meuemail@dominio.com.br', 
    @subject = 'Testando envio automatico do SQL Server', 
    @body = 'Funcionou o envio do e-mail', 
    @body_format = 'text'
    
    end;

    segunda-feira, 20 de maio de 2019 18:44
  • Edvaldo,

    Certo, podemos completar o código colocando uma condição:

    alter TRIGGER TestMyTable_Upd
    ON dbo.tb_planos_acoes
    AFTER UPDATE
    AS
    BEGIN
    Declare @Codigo Int
    
    Select @Codigo = Codigo From Inserted
    Where Status_Fim = 'Encerrado'
    
    Update TB_Planos_Acoes
    Set DT_Trigger = GetDate()
    Where Codigo = @Codigo
    And Status_Fim = 'Encerrado'
    
    
    If (Select Status_Fim From Inserted Where Código = @Codigo) = 'Encerrado'
     Execute P_EnviarEmail 
    Else
     Return
    end;
    
    
    ------- PROCEDURE -----------
    alter Procedure P_EnviarEmail
    As
    Begin
    exec msdb.dbo.sp_send_dbmail 
    @profile_name = 'MailTest6', 
    @recipients = 'meuemail@dominio.com.br', 
    @subject = 'Testando envio automatico do SQL Server', 
    @body = 'Funcionou o envio do e-mail', 
    @body_format = 'text'
    
    end;
    Veja se isso te ajuda.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]


    segunda-feira, 20 de maio de 2019 18:50
    Moderador
  • Edvaldo,

    Certo, podemos completar o código colocando uma condição:

    alter TRIGGER TestMyTable_Upd
    ON dbo.tb_planos_acoes
    AFTER UPDATE
    AS
    BEGIN
    Declare @Codigo Int
    
    Select @Codigo = Codigo From Inserted
    Where Status_Fim = 'Encerrado'
    
    Update TB_Planos_Acoes
    Set DT_Trigger = GetDate()
    Where Codigo = @Codigo
    And Status_Fim = 'Encerrado'
    
    
    If (Select Status_Fim From Inserted Where Código = @Codigo) = 'Encerrado'
     Execute P_EnviarEmail 
    Else
     Return
    end;
    
    
    ------- PROCEDURE -----------
    alter Procedure P_EnviarEmail
    As
    Begin
    exec msdb.dbo.sp_send_dbmail 
    @profile_name = 'MailTest6', 
    @recipients = 'meuemail@dominio.com.br', 
    @subject = 'Testando envio automatico do SQL Server', 
    @body = 'Funcionou o envio do e-mail', 
    @body_format = 'text'
    
    end;
    Veja se isso te ajuda.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]


    Funcionou ! Obrigado !!!

    Mais uma ajudinha,  como eu faço para informar algumas colunas no corpo e-mail ?

    Outra questão ...

    Criei uma view para visualizar os e-mails enviados

    CREATE VIEW [dbo].[vwMonitoramento_Email] AS
    SELECT
        a.send_request_date AS DataEnvio,
        a.sent_date AS DataEntrega,
        (CASE    
            WHEN a.sent_status = 0 THEN '0 - Aguardando envio'
            WHEN a.sent_status = 1 THEN '1 - Enviado'
            WHEN a.sent_status = 2 THEN '2 - Falhou'
            WHEN a.sent_status = 3 THEN '3 - Tentando novamente'
        END) AS Situacao,
        a.from_address AS Remetente,
        A.recipients AS Destinatario,
        a.subject AS Assunto,
        a.reply_to AS ResponderPara,
        a.body AS Mensagem,
        a.body_format AS Formato,
        a.importance AS Importancia,
        a.file_attachments AS Anexos,
        a.send_request_user AS Usuario,
        B.description AS Erro,
        B.log_date AS DataFalha
    FROM 
        msdb.dbo.sysmail_mailitems                  A    WITH(NOLOCK)
        LEFT JOIN msdb.dbo.sysmail_event_log        B    WITH(NOLOCK)    ON A.mailitem_id = B.mailitem_id

    No futuro terei problemas em armazenar estas informações, porque estou usando o SQL Server express, posso "limpar " estes dados ? como ?

    segunda-feira, 20 de maio de 2019 19:00
  • Edvaldo, 

    Que bom, vamos lá:

    1 - Em relação as colunas no corpo do e-mail, basta concatenar as colunas com o texto informado na opção body_text: @body = 'Funcionou o envio do e-mail' +SuaColunaAqui + 'outro texto....' + OutraColuna.....

    Então, talvez você possa estar fazendo confusão, uma View é uma representação estática das colunas de uma tabela e do conjunto de elementos declarados no código fonte da view, ela não possui dados armazenados fisicamente a ela, a não ser que seja uma view indexada, o que na verdade aplica-se um índice na view, mas que também fica amarrado e vinculado a tabela e coluna que esta sendo utilizada na view.

    Agora, os dados armazenados nas tabelas que são utilizadas pelo Database Mail, podem ser removidas da mesma forma que os dados armazenados em nossas tabelas.

    Veja estes exemplos:

    USE MSDB;
    GO
    
    DELETE FROM msdb.dbo.sysmail_allitems
    WHERE ((@sent_before IS NULL) OR ( send_request_date < @sent_before))
       AND ((@sent_status IS NULL) OR (sent_status = @sent_status))
    
    -- Ou --
    DECLARE @DeleteBeforeDate DateTime
    
    SELECT @DeleteBeforeDate = DATEADD(d,-30, GETDATE())
    
    EXEC sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDate
    
    
    EXEC sysmail_delete_log_sp @logged_before = @DeleteBeforeDate
    
    -- Ou --
    
    DELETE FROM msdb.dbo.sysmail_allitems
    
    DELETE FROM msdb.dbo.sysmail_log
    Go


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]


    segunda-feira, 20 de maio de 2019 19:07
    Moderador
  • Deleted
    segunda-feira, 20 de maio de 2019 19:21
  • Edvaldo, 

    Que bom, vamos lá:

    1 - Em relação as colunas no corpo do e-mail, basta concatenar as colunas com o texto informado na opção body_text: @body = 'Funcionou o envio do e-mail' +SuaColunaAqui + 'outro texto....' + OutraColuna.....

    Então, talvez você possa estar fazendo confusão, uma View é uma representação estática das colunas de uma tabela e do conjunto de elementos declarados no código fonte da view, ela não possui dados armazenados fisicamente a ela, a não ser que seja uma view indexada, o que na verdade aplica-se um índice na view, mas que também fica amarrado e vinculado a tabela e coluna que esta sendo utilizada na view.

    Agora, os dados armazenados nas tabelas que são utilizadas pelo Database Mail, podem ser removidas da mesma forma que os dados armazenados em nossas tabelas.

    Veja estes exemplos:

    USE MSDB;
    GO
    
    DELETE FROM msdb.dbo.sysmail_allitems
    WHERE ((@sent_before IS NULL) OR ( send_request_date < @sent_before))
       AND ((@sent_status IS NULL) OR (sent_status = @sent_status))
    
    -- Ou --
    DECLARE @DeleteBeforeDate DateTime
    
    SELECT @DeleteBeforeDate = DATEADD(d,-30, GETDATE())
    
    EXEC sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDate
    
    
    EXEC sysmail_delete_log_sp @logged_before = @DeleteBeforeDate
    
    -- Ou --
    
    DELETE FROM msdb.dbo.sysmail_allitems
    
    DELETE FROM msdb.dbo.sysmail_log
    Go


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]


    Com relação a view me expressei mal. Obrigado
    segunda-feira, 20 de maio de 2019 19:49
  • 2 - Os campos que vou enviar por email são : codigo, designacao, cliente e status_fim

    Ok, já atualizei o código #1 v4, "lá em cima". 

    Para você terminar o código, basta acrescentar a declaração dos tipos de dados para codigo, designacao, cliente e status_fim. É onde está o ___, ok?


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


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

    Gerou um erro:

    Mensagem 102, Nível 15, Estado 1, Procedimento nometrigger, Linha 44
    Sintaxe incorreta próxima a '+'.

    segunda-feira, 20 de maio de 2019 19:52
  • Deleted
    segunda-feira, 20 de maio de 2019 20:49
  • José e Junior obrigado pelo auxilio, consegui implementar o código.Abraço!
    terça-feira, 21 de maio de 2019 14:01