Trigger para enviar email quando é realizado update

Question

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.

Answer 1

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 = '[e-mail]', 
 @subject = 'Testando envio automatico do SQL Server', 
 @body = 'Funcionou o envio do e-mail', 
 @body_format = 'text'
end;

Answer 2

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

Answer 3

Deleted

Answer 4

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,

Answer 5

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

Answer 6

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

Answer 7

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]

Answer 8

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 = '[e-mail]', 
     @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 = '[e-mail]', 
@subject = 'Testando envio automatico do SQL Server', 
@body = 'Funcionou o envio do e-mail', 
@body_format = 'text'

end;

Answer 9

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

Answer 10

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

Answer 11

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]

Answer 12

Deleted

Answer 13

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

Answer 14

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 '+'.

Answer 15

Deleted

Answer 16

José e Junior obrigado pelo auxilio, consegui implementar o código.Abraço!