Usuário com melhor resposta
Trigger para enviar email quando é realizado update

Pergunta
-
Respostas
-
-
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]
- Editado Junior Galvão - MVPMVP, Moderator segunda-feira, 20 de maio de 2019 18:50
- Marcado como Resposta Edvaldo A segunda-feira, 20 de maio de 2019 19:49
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;
-
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
-
-
-
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
-
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]
- Editado Junior Galvão - MVPMVP, Moderator segunda-feira, 20 de maio de 2019 18:32
-
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; goNã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]
- Editado Junior Galvão - MVPMVP, Moderator terça-feira, 21 de maio de 2019 12:30 Removida a minha citação.
-
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;
-
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]
- Editado Junior Galvão - MVPMVP, Moderator segunda-feira, 20 de maio de 2019 18:50
- Marcado como Resposta Edvaldo A segunda-feira, 20 de maio de 2019 19:49
-
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 ?
-
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]
- Editado Junior Galvão - MVPMVP, Moderator segunda-feira, 20 de maio de 2019 19:08
-
-
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]
-
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 '+'.
-
-