Re: sending an email from a table trigger (before inserting row)

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Mon, 07 Jul 2003 14:01:31 +0200
Message-ID: <3F09611B.1030209@science-computing.de>

Jeremy wrote:
> In article <1a75df45.0307062152.145a6bbd_at_posting.google.com>,
> vslabs_at_onwe.co.za says...
>

>>Jeremy <newspostings_at_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.

My (not exactly my but derived from Tom Kytes book "expert one-on-one") 2c

Holger Received on Mon Jul 07 2003 - 07:01:31 CDT