Re: Is there any way to Mail , when a new record is inserted.

On 15 Jan 1998 09:07:23 GMT, "Johnny Chopra" <johnnyc_at_mail.starnet.com.au> wrote:

>Hi Everyone,
>
>I have a requirement that whenever a new record is inserted into a table
>for e.g. EMP, an e-mail message should be sent to a specific
>User for e.g. SCOTT.
>
>I tried to write a database trigger (After Insert on EMP) as below:
> CREATE OR REPLACE TRIGGER MAIL_NEW_EMP_TRIG
> AFTER INSERT
> ON EMP
> BEGIN
> HOST ('/home/scott/mail_new_emp.sh');
> END;
>
>This work fine when I run from sqlprompt
>SQL> HOST /home/scott/mail_new_emp.sh
>
>But It does not work from inside trigger.
>Is there any way to mail when a new record is inserted into a table.

The reason HOST doesn't work in a trigger is because it's not a PL/SQL command, it's a SQLPLUS command. Triggers are 100% PL/SQL.

I would do something like this.

1. Create a table that looks like this: create table send_message. ( flag varchar2(1) )
2. Write an insert trigger that inserts a row (doesn't matter what flad is set to) into the table whenever you want to send an email.
3. Write a shell script that cron fires off every few minutes that queries the table and sends runs your email procedure if it finds anything in the table. It would look like this. #!/bin/sh # You can set the ConnectString however you want as long as # it contains a valid connect string for a user that can access # the send_message table; ConnectString="your_user/your_password_at_your_db" sqlplus -s <<! | read Count $ConnectString set pages 0 feedback off select count(*) from send_message; truncate table send_mesage; ! [ $Count -gt 0 ] && mail_new_emp.sh -- Chuck Hamilton chuckh_at_dvol.com

Never share a foxhole with anyone braver than yourself! Received on Fri Jan 16 1998 - 00:00:00 CST