[cecodeel]

Hi,

I am using Oracle 8.1.7 on a Windows 2000 Server.
I want to generate an E-mail when a specific table is updated.
Can someone tell me what the best way is to do this ?

Thanks.

Cecodeel

[andrewst]

Originally posted by cecodeel
Hi,

I am using Oracle 8.1.7 on a Windows 2000 Server.
I want to generate an E-mail when a specific table is updated.
Can someone tell me what the best way is to do this ?

Thanks.

Cecodeel

Create an AFTER UPDATE trigger on the table. Use the UTL_SMTP package to send the email. However, there are 2 issues with this:
1) UTL_SMTP is pretty slow, which will impact your performance
2) UTL_SMTP will send out the email even if the update is rolled back.

These issues can be overcome by using DBMS_JOB as follows:
- Create a stored procedure that uses UTL_SMTP to send an email
- In the trigger, call DBMS_JOB.SUBMIT to immediately run your stored procedure

This overcomes the above issues because:
1) The job is run asynchronously, so the trigger does not have to wait for the job to complete
2) The job only runs when the update is committed, and not at all if it is rolled back.

If you have Tom Kyte's book "Expert One On One Oracle", he shows a complete implementation of this approach.

[cecodeel]

Originally posted by andrewst
Create an AFTER UPDATE trigger on the table. Use the UTL_SMTP package to send the email. However, there are 2 issues with this:
1) UTL_SMTP is pretty slow, which will impact your performance
2) UTL_SMTP will send out the email even if the update is rolled back.

These issues can be overcome by using DBMS_JOB as follows:
- Create a stored procedure that uses UTL_SMTP to send an email
- In the trigger, call DBMS_JOB.SUBMIT to immediately run your stored procedure

This overcomes the above issues because:
1) The job is run asynchronously, so the trigger does not have to wait for the job to complete
2) The job only runs when the update is committed, and not at all if it is rolled back.

If you have Tom Kyte's book "Expert One On One Oracle", he shows a complete implementation of this approach.

Ok
Thanks

Cecodeel