Criar um TRIGGER para enviar email

[pedro.peixoto]

Boas a todos,

Desta vez o que me traz aqui é para pedir a vossa ajuda para criar uma trigger para enviar um email sempre que o campo de uma tabela seja actualizado e o seu valor seja menor ou igual a 1.

Então a situação é esta

tenho uma tabela de clientes que tem um campo [Plafond decimal(14, 2)] e um outro que é o campo [vendedor char(4)]

aquilo que pretendo é, sempre que o campo plafond seja menor ou igual a 1, que envie um email ao vendedor 0001 (por exemplo)

O email do vendedor 0001 está numa tabela de uma outra base de dados mas dentro do mesmo servidor. que é a tabela Utilizadores.

O pior de tudo é que em triggers e stored procedures eu sei menos do que o básico 🙂

Pelo que andei a pesquisar seria algo do género

CREATE TRIGGER EnviaEmail
ON Clientes
FOR UPDATE
AS
IF ((SELECT plafond FROM updated) <= 1)
BEGIN
 SET NOCOUNT ON
 USE msdb exec sp_send_dbmail
	 @profile_name='PlafondProfile',
	 @recipients='EMAIL DO VENDEDOR',
	 @subject='Alteração do Plafond',
	 @body='O plafond do cliente X foi alterado para Y'

 SET NOCOUNT OFF
END

Agora a minha dúvida é como faço um select à tabela dos Utilizadores para receber o email do vendedor 0001 e atribuo-o ao compo @recipients.

Sabendo esta parte, colocar o nome do cliente e o plafond no corpo da mensagem não deverá ser nada de outro mundo... (penso eu)

Obrigado a todos

[Rechousa]

Viva,

Não sou apologista dessa abordagem, criar um trigger para enviar um email. Em alternativa, usaria o trigger para inserir um registo numa tabela e, ter uma tarefa agendada para envio de emails (mediante os registos nessa tabela).

Não uses o CREATE TRIGGER FOR UPDATE, mas sim CREATE TRIGGER AFTER UPDATE (a não ser que queiras mesmo ter o comportamento que vou descrever). A diferença é que se o sistema de envio de email falhar usando FOR UPDATE vai fazer com que o trigger aborte e o registo não seja alterado. Se usares AFTER UPDATE, o registo foi alterado e o trigger é disparado depois. Mesmo que aborte, o pior que pode acontecer é não enviar o email - mas lá está, se o comportamento que desejas é que garanta o envio do email, então usa o FOR UPDATE.

Quanto à dúvida:

Deverás conseguir à variável da mesma forma que acedes ao plafond.

Seria +/- isto (não testei):

CREATE TRIGGER EnviaEmail
ON Clientes
AFTER UPDATE
AS
SET NOCOUNT ON;

DECLARE @plafond NUMERIC;
DECLARE @CodigoVendedor VARCHAR(20);
DECLARE @EmailVendedor VARCHAR(200);

SELECT @plafond = plafond, @CodigoVendedor = CodigoVendedor FROM updated;

IF (@plafond <= 1)
BEGIN
  SELECT @EmailVendedor = Email FROM BaseDadosVendedores.dbo.Vendedores WHERE Codigo=@CodigoVendedor;

  IF @EmailVendedor IS NOT NULL
  BEGIN
  USE msdb exec sp_send_dbmail
	@profile_name='PlafondProfile',
	@recipients='EMAIL DO VENDEDOR',
	@subject='Alteração do Plafond',
	@body='O plafond do cliente X foi alterado para Y'
  END
END

Espero ter ajudado.

[pedro.peixoto]

Obrigado pela explicação, já ficou resolvido. Mas essa questão de inserir um registo noutra tabela também é pertinente, para já fica desta forma

CREATE TRIGGER EnviaEmail
ON TabelaCliente
AFTER UPDATE
AS
IF ((SELECT Plafond FROM INSERTED) <= 1)
BEGIN
SET NOCOUNT ON
declare @email nvarchar(50)
declare @clienteID int
declare @vendedorID char(4)
declare @texto nvarchar(150)
select @clienteID = ClienteID from INSERTED
select @texto = 'ATENÇÃO o plafond do seu cliente - ' +
cast(TabelaCliente.ClienteID as nvarchar)+' - '
+ rtrim(Tabelaterceiro.Nome)+ ' foi alterado para '
+ cast(TabelaCliente.Plafond as nvarchar)+'€'
from TabelaCliente
inner join TabelaTerceiro
on TabelaCliente.TerceiroID = Tabelaterceiro.TerceiroID
where ClienteID=@clienteID
select @email= email from OutraBD.dbo.Utilizadores
where codigo=@vendedorID
select @texto
select @email
exec msdb.dbo.sp_send_dbmail
@profile_name='PlafondProfile',
@recipients=@email,
@subject='ATENÇÃO - Alteração do Plafond',
@body=@texto

SET NOCOUNT OFF
END

Edited September 19, 2012 at 01:43 PM by pedro.peixoto

[Rechousa]

Viva,

A vantagem de acrescentares o registo na outra tabela é que funcionaria também como LOG 😉