Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!news.cc.ukans.edu!logbridge.uoregon.edu!fu-berlin.de!uni-berlin.de!212.42.175.225!not-for-mail
From: Jeremy <newspostings@hazelweb.co.uk>
Newsgroups: comp.databases.oracle.server
Subject: Re: sending an email from a table trigger (before inserting row)
Date: Mon, 7 Jul 2003 13:16:36 +0100
Lines: 52
Message-ID: <MPG.19737503ebcc01d4989798@news.cis.dfn.de>
References: <be0irt$9b1$1@unbe.sarenet.es> <1a75df45.0307030425.febba27@posting.google.com> <MPG.196e4518bab3169398978d@news.cis.dfn.de> <1a75df45.0307062152.145a6bbd@posting.google.com> <MPG.1973669b2e93eb88989795@news.cis.dfn.de> <3F09611B.1030209@science-computing.de>
NNTP-Posting-Host: 212.42.175.225
X-Trace: fu-berlin.de 1057580192 3566118 212.42.175.225 (16 [140241])
X-Newsreader: MicroPlanet Gravity v2.50
Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:237048

In article <3F09611B.1030209@science-computing.de>, holger.baer@science-
computing.de says...
> Jeremy wrote:
> > In article <1a75df45.0307062152.145a6bbd@posting.google.com>, 
> > vslabs@onwe.co.za says...
> > 
> >>Jeremy <newspostings@hazelweb.co.uk> wrote 
> >>
> >>
> >>>I don't think Jon was necessarily saying that the trigger was going to 
> >>>do validation per se - perhaps just that under certain data conditions, 
> >>>he would like to be able to send an e-mail based on an update to a 
> >>>table. If you read it that way, do you have the same opinion?
> >>
> > 
> > <SNIP>
> > 
> >>Know what we are at the end of the day Jeremy? Problem solvers. From
> >>DBA's to developers. What determines the difference between good and
> >>bad problem solvers? How well and to what extent a problem is solved.
> >>You want to tell me that that a crude method like send mail in a
> >>trigger can truly solve the problem, never mind  that it will be
> >>introducing a whole bunch of other problems?
> > 
> > 
> > Hmm... interesting pov. I guess I just didn't see anything fundamentally 
> > wrong with the concept of sending e-mail from within a trigger - agree 
> > that if talking directly to the smtp server it is definitely a no-no, 
> > but as a means of creating a 'request' processed separately by a 
> > procedure under DBMS_JOB? Do you say that you should never initiate (by 
> > which I mean log a request for later processing) an email from within a 
> > DB trigger?
> > 
> > 
> 
> Well, I think it depends on what you mean by 'initiate'. If you use dbms_job
> to schedule the mail to be sent later, you've initiated the mail and there
> is nothing fundamentally wrong with that.
> 
> But of course, you should not directly talk to the smtp server from the trigger.
> It just adds a large overhead to the trigger, which will not only greatly reduce
> the possible throughput, but also you loose the transactional consistency:
> If you send the mail through the db trigger, that mail is going to be sent
> no matter if the transaction succeeds or is rolled back. By using dbms_job,
> the mail is only sent if the transaction is committed.
> 

makes absolute sense

-- 

jeremy