Trigger para envio de email

[KhaosDoctor 242]

Olá galera,

 

Estou enfrentando um problema que só pode ser resolvido desta maneira. Preciso enviar emails para clientes avisando que eles possuem contas a pagar em determinado dia, até ai tenho todos os dados armazenados no meu banco, como o email do cliente, o tipo de conta, a data de pagamento e etc.

 

O que eu preciso que o banco faça é que: Todo dia em um determinado horário ele verifique se há clientes no banco que possuem contas a pagar nos próximos dois dias, depois ele deve verificar o email destes clientes que possuem as contas a pagar através de um select que irá pegar apenas os registros cuja permissão no sistema é de administrador(também armazenado na tabela) e irá escrever um email dizendo que há uma conta para ser paga neste dia.

 

Ele deve informar o tipo de conta(que está no formato de ID com chave em outra tabela) e o dia que ela deve ser paga, para cada conta o email deve ser enviado tres vezes: uma dois dias antes, um dia antes e outro no dia, e para cada uma seria um email diferente.

 

O problema é que não sei como posso realizar este trigger, pegando a data de hoje e tudo mais.

 

Quem puder ajudar eu agradeço.

[advaldomesquita 93]

Pode ser por JOB?

Acho bem mais viavel....

Qual a versão do SQL? Você pode usar o DATABASEMAIL para este tipo de envio, mas não pode ser a versão express.....

 

Manda ai a sua versão do SQL que eu te ajudo a montar rapidinho isso.

 

[]´s

[KhaosDoctor 242]

Eu estou usando a versão 2008 do SQL Server, já tentei utilizar com o xp_sendemail e outras funções que encontrei na internet, mas eu preciso da verificação e também aprender um pouco mais sobre triggers, já que nunca tive necessidade de utilizá-los.

[advaldomesquita 93]

Bom, vamos por partes.....

 

Envio de email no SQL2008, vc tem que ter um email válido, endereço de SMTP, mas como já tentou executar a "xp_sendemail" deve ter estes dados.

 

Databasemail, conseguiu configurar?

Eu uso para envio de email a "sp_send_dbmail" que fica na base MSDB.

Sua sintaxe é muito simples:

 

[sq]l

 

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'central',

@importance = 'High',

@recipients = 'adjuniordba@googlemail.com',

-- @copy_recipients = ';

@subject = 'Processos' ,

@body_format = 'HTML' ,

@body = 'Processos a serem executados'

[/sql]

Para este uso, tem que estar configurado o DATABASEMAIL.

Faça deste modo:

Logue na instancia com um usuário SYSADMIN

Gerenciamento

Database Mail - Clique com o botão direito do mouse --> Configurar Database Mail

Nesta tela vc terá diversas opções, mas vamos a primeira: Instalar Database Mail....

Nome do perfil: Você defini um nome que vai ser utilizado na procedure acima;

Descrição: Não é obrigatório.

Clique em adicionar (do lado direito)

Clique em NOVA CONTA

Nome da conta: Nome que vai ser tulizado, caso preciso dar permissão especifica a usuarios, em tese, coloque o mesmo nome do perfil.

Descrição: Não é obrigatório.

Note que tem uma parte de SMTP!!!

Coloque um endereco de email VALIDO e o endereco SMTP, porta, senha se for o caso.

Clique em ok nas duas janelas e na do PERFIL, clique em avançar

Deve estar setado como PERFIL PUBLICO, mantenha assim, pois deste modo todos os usuarios terao acesso a este perfil. Clique em Avançar.

PARAMETROS DO SISTEMA, deixe como esta, para não ter dor de cabeça.

e depois CONCLUIR

Parece muita coisa, mas não é.

Tente configurar o Database Mail antes de eu te passar o esquema da JOB.

Eu até colocaria imagens, mas estou sem acesso, e mais tarde eu posto no meu blog.

[]´s

[KhaosDoctor 242]

Mesmo para o xp_sendmail é necessário fazer isso? Pois o db é hospedado no servidor e não sei se eu consigo entrar nas configurações do banco...

[advaldomesquita 93]

Mesmo para o xp_sendmail é necessário fazer isso? Pois o db é hospedado no servidor e não sei se eu consigo entrar nas configurações do banco...

Este recurso vai ser descontinuado:

http://msdn.microsoft.com/pt-br/library/ms189505(v=sql.105).aspx

 

Mas vamos lá:

Para ser utilizada, tem que ter um cliente de email instalado, como o OUTLOOK ( e não o EXPRESS).

Acontece, se você não tiver instalado, dar este erro:

 

Mensagem 17930, Nível 16, Estado 1, Linha 0

xp_sendmail: Ou não há cliente de correio padrão, ou o cliente de correio atual não pode atender à solicitação de envio de mensagem. Execute o Microsoft Outlook e defina-o como cliente de correio padrão.

Tente executar direto a sintaxe abaixo no banco:

EXEC master.dbo.xp_sendmail

@recipients=N'danw@Adventure-Works.com',

@message=N'The master database is full.' ;

Uma pergunta: tem acesso master a sua base? ou ele esta hospedado em um provedor?

[Motta 645]

Tome cuidado com o volume de emails enviados para não cair indevidamente em listas de spammers.

Não sei ao certo mas existem algum protocolos que regulam este tipo de envio.

No seu caso perfeitamente legal.

[advaldomesquita 93]

Tome cuidado com o volume de emails enviados para não cair indevidamente em listas de spammers.

Não sei ao certo mas existem algum protocolos que regulam este tipo de envio.

No seu caso perfeitamente legal.

No caso do SQLServer, o email em questão tem que estar configurado com o DNS Reverso e MX de forma a não cair na caixa de SPAM. Tem que ser verificado com o pessoal de infra e/ou suporte.

[KhaosDoctor 242]

Ele está hospedado em um provedor, este é o meu medo, pois este banco não está criado aqui. Tenho acesso total à minha database no servidor mas não sei se posso configurar tudo aquilo que você me disse.

 

 

Vou tentar fazer o trigger sem ver estas configurações primeiro, caso aconteça algum erro eu irei postando aqui.

 

Como ficaria o trigger em questão? Procurar na database de contas os dias de pagamento de cada conta e associá-los as empresas que devem pagar? Lembrando que apenas os usuários administradores de cada empresa devem receber o email. Meu database está configurado assim:

 

Tbtiposcomprovante

id[PK]

idemp (id da empresa)[FK com id da tabela empresa]

tipo

diapagamento

 

Tbemp

id[PK]

dados de cadastro

 

Tbuser

id[PK]

login

senha

email

idemp[FK com id da empresa]

tipo (define o nível de usuário)

 

Eu preciso selecionar todas as empresas da tabela de tipos juntamente com os respectivos tipos e dias de pagamento, depois preciso fazer um select pelo tipo de usuário administrador (id 2) onde a empresa é igual a selecionada e pegar o email dele para poder enviar uma mensagem do tipo:

 

"Você possui um comprovante do tipo @tipo para ser pago no dia @dia"

 

Onde @tipo seria o tipo de comprovante em questão e o @dia seria o dia de pagamento deste tipo, mas uma empresa pode ter mais de um administrador e o sistema deve enviar emails para todos eles.

 

A query eu tenho em mente, porém preciso saber se ao adicionar esta query no trigger ele pegará somente os dados que eu quero, sem me retornar todos os usuários... E eu também não possuo conhecimento de como fazer a verificação das datas no trigger, para ele ser realizado uma vez ao dia, verificando todos os comprovantes que devem ser pagos nos próximos dois dias seguintes.

 

Obs: Tentei configurar o DBMail, mas não possuo sysadmin, creio que o servidor já o tenha configurado para uso, porém não sei como foi feito.

[advaldomesquita 93]

A trigger é disparada em algum momento pela tabela, seja por inserção, deleção de dados (INSERT, DELETE) por exemplo. Como pensa em usa-la?

 

Vou criar um ambiente de teste aqui para fazer este select ....

[KhaosDoctor 242]

Então, ele deve ser automático, um sistema passivo que deveria ficar rodando no SQL e faria uma verificação sempre que uma determinada hora de cada dia chegasse, então ele enviaria os emails, é o mesmo esquema do sistema de newsletters, porém com avisos de pagamento.

[advaldomesquita 93]

Então, ele deve ser automático, um sistema passivo que deveria ficar rodando no SQL e faria uma verificação sempre que uma determinada hora de cada dia chegasse, então ele enviaria os emails, é o mesmo esquema do sistema de newsletters, porém com avisos de pagamento.

Automático, somente por JOB. Dá para criar manualmente, mas vamos por partes.

 

Fiz um teste assim:

   declare @Tbtiposcomprovante table (id int identity (1,1) , -- [PK]idemp int , -- (id da empresa)[FK com id da tabela empresa]tipo varchar(max), diapagamento datetime ) declare @Tbemp table (id int identity (1,1),  -- [PK]dados varchar(max) --de cadastro ) declare @Tbuser table (id int identity (1,1),  --[PK]login varchar(max) , senha varchar(max) , email varchar(max) ,idemp int , -- [FK com id da empresa]tipo varchar(2) -- (define o nível de usuário))insert into @Tbemp values ('dado1')insert into @Tbemp values ('dado2')insert into @Tbuser values ('login', 'senha', 'email', 1,1)insert into @Tbtiposcomprovante values (1,1,'2013-05-15')  /* VARIAVEIS A SEREM UTILIZADAS PARA MONTAR O ENVIO DE EMAIL */ declare @textinicio varchar(MAX) declare @textfim varchar(MAX) declare @email varchar(MAX)  set @textinicio = ' Você possui um comprovante do tipo ' set @textfim = ' para ser pago no dia ' select @textinicio + cmp.tipo + @textfim + convert(varchar(max), cmp.diapagamento)-- selecionar todas as empresas da tabela de tipos juntamente com os respectivos tipos e dias de pagamentofrom @Tbemp emp inner join @Tbtiposcomprovante cmp on emp.id = cmp.idempinner join @Tbuser us on emp.id = us.idemp-- um select pelo tipo de usuário administrador (id 2) onde a empresa é igual a selecionada e pegar o email delewhere us.tipo = 1 

Veja se roda no seu ambiente ou se tem algo muito errado.

[KhaosDoctor 242]

certo entendi, o cmp seria o que?

Já resolvi esse do cmp :D

---

 

Ele me mostra o seguinte erro.

 

 

 

Msg 207, Level 16, State 1, Line 9
Invalid column name 'tipo'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'diapagamento'.

Consegui resolver este erro também, era só um problema com nomes que eu coloquei aqui. O resultado deu certinho, só falta inserir no trigger.

[advaldomesquita 93]

Como eu falei, vamos por partes:

 

Para mandar email:

 

Veja se funciona o envio de email:

 exec xp_sendmail  @recipients = '' -- LISTA DE EMAILS      , @message = 'Envio de email'      , @query = '' -- sintaxe ou proc!!!     , @subject = 'TESTE DE ENVIO DE EMAIL'     , @type = 'HTML' -- VALOR FIXO, para melhor visuzlização

[KhaosDoctor 242]

Não funcionou:

 

 

Msg 229, Level 14, State 5, Procedure xp_sendmail, Line 1

The EXECUTE permission was denied on the object 'xp_sendmail', database 'mssqlsystemresource', schema 'sys'.

[advaldomesquita 93]

Cocê não tem permissão para mandar email pelo banco de dados.

Pode-se pedir esta permissão ao seu provedor/infra e/ou fazer uma pagina em php, aspx, ou outra plataforma com componentes para envio de email.

 

Infelizmente, no seu caso, via banco de dados não é possível resolver :(

[KhaosDoctor 242]

Eu mandei uma mensagem ao host, mas creio que o que tenha mesmo que fazer seja criar um programinha para rodar offline mesmo.

 

Obrigado pela ajuda!

[advaldomesquita 93]

Eu mandei uma mensagem ao host, mas creio que o que tenha mesmo que fazer seja criar um programinha para rodar offline mesmo.

 

Obrigado pela ajuda!

Eu duvido que o host irá dar este tipo de permissão. Infelizmente, eles não tem uma definição de permissão muito concreta, pois em 99% dos casos, qm toma conta dos servidores de banco de dados não são DBA´s e sim desenvolvedores que não conhecem a ferramenta de sua forma plena.

Tive problemas com LOCAWEB, ALOG entre outras por causa deste tipo de permissão.

 

Boa sorte, se precisar de algo só postar.

 

[]´s

[KhaosDoctor 242]

Infelizmente não sou eu quem tem a liberdade de escolher o servidores aqui rs então o que temos eu tenho quase certeza de que não será feito.

 

Mas obrigado pela ajuda.