Re: Oracle email/trigger

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Mon, 22 Apr 2002 14:57:49 +0400
Message-ID: <aa0q99$8f3$1@babylon.agtel.net>

create or replace trigger TRG$BIUD$tbltest before insert or update or delete on tbltest for each row
declare
  dummy number;
  job_stmt varchar2(32000) := 'declare
  msg psp_mail.Strings;
  ret psp_mail.Strings;
begin
 msg(1) := ''Something changed in the table tbltest'';  ret := psp_mail.SendMailEx2(v_from => ''oracle_at_company.com'',

   v_to => ''alerts_at_company.com'',
   v_subject => ''alert from Oracle'',
   v_body => msg,

   attachments => psp_mail.TBAtt_Empty); end;';

begin
-- we'll use the job here, because job submit can be rolled back -- and we don't want any email to go out for uncommitted transactions dbms_job.submit(dummy, job_stmt);
end;

PSP_Mail is our package for Oracle8i R2 or later that wraps around UTL_SMTP and provides easy to use API to send any type of email from the db. It can also do DNS lookups and send mail directly to recipient's mail server, supports unlimited number of attachments (CLOBs or BLOBs) and a lot more. Of course, you can use other tools for mail transport - the idea stays the same: you just submit a job that does actual send and when transaction is committed, the job is picked up by SNP and processed asynchronously. Good thing is that when transaction rolls back, job submission rolls back either and no mail is sent. If you send directly from trigger, chances are there will be unwanted email sent for transactions that were later rolled back.

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Monty" <mmontreaux_at_hotmail.com> wrote in message
news:6284dd3.0204220010.4bf70c2f_at_posting.google.com...


> Hello - just to add to the recurring questions on Oracle & email,  can

> I ask for someone to post the absolute minimum I have to write a

> trigger for the table below to fire off an email message to inform

> someone that the table has changed.

>

> CREATE TABLE tbltest(x NUMBER)

>

>

> I don't care who the email is sent off to, or under what condition the

> trigger fires, just wanting to establish that it can be done as proof

> of concept.

>

> Our server has WinNT 4.5, and Oracle 8, MS Exchange, and doubt it's

> relevant but local email client MS OutLook.

>

> Thanks

> Monty

Received on Mon Apr 22 2002 - 05:57:49 CDT