Обсуждение: Triggers that send emails

Hi:

Thanks in part to your help, I've now got a few simple queries and forms
running on my website, using PHP and PEAR to talk to Postgres. :-)

In one case, I've set up a form where users can provide feedback for me.
This form causes an INSERT to a table, and appears to be working well;
but if I don't check the table manually, I'll never know when someone
left me a note!

I am aware that "triggers" or "stored procedures" or something like that
will be able to run a process every time the table is updated, and that
this is what I will need to use to make the database email me every time
someone leaves feedback. I've just started reading about triggers in the
manual for this purpose.

However, so as not to reinvent the wheel, is there a code snippet out
there which is public domain and which will show me easily how to use a
trigger to send an email message? I have not found one yet with Google,
so I thought I'd ask here.

Thanks!

--
Rodolfo J. Paiz <rpaiz@simpaticus.com>

Well, I confess.  Although I've several databases running on postgreSQL
I've never done more than just store data. (no constaints, no triggers, no
functions, not even any transactions) I'm setting up a new schema for a
new database and am learning about using foreign keys.

Is there a SQL command that removes both a row in a table and the rows in
other tables that reference it or do I have to do it manually?

Sort of like 'DELETE CASCADE....." which I tried, to no avail. Perusing
the manual didn't reveal anything....

brew

 ==========================================================================
                  Strange Brew   (brew@theMode.com)
  Check out my Stock Option Covered Call website  http://www.callpix.com
     and my Musician's Online Database Exchange http://www.TheMode.com
 ==========================================================================

On Mon, Apr 04, 2005 at 02:56:56AM -0400, brew@theMode.com wrote:
>
> Is there a SQL command that removes both a row in a table and the rows in
> other tables that reference it or do I have to do it manually?

If you define the foreign key constraints as ON DELETE CASCADE,
then referencing rows will be automatically deleted when you DELETE
the referenced row.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael.....

> > Is there a SQL command that removes both a row in a table and the rows in
> > other tables that reference it or do I have to do it manually?
>
> If you define the foreign key constraints as ON DELETE CASCADE,
> then referencing rows will be automatically deleted when you DELETE
> the referenced row.

Ah Ha, thanks muchly!!!  That's just what I was looking for.

brew

 ==========================================================================
                  Strange Brew   (brew@theMode.com)
  Check out my Stock Option Covered Call website  http://www.callpix.com
     and my Musician's Online Database Exchange http://www.TheMode.com
 ==========================================================================

On Mon, Apr 04, 2005 at 02:56:56 -0400,
  brew@theMode.com wrote:
>
> Well, I confess.  Although I've several databases running on postgreSQL
> I've never done more than just store data. (no constaints, no triggers, no
> functions, not even any transactions) I'm setting up a new schema for a
> new database and am learning about using foreign keys.
>
> Is there a SQL command that removes both a row in a table and the rows in
> other tables that reference it or do I have to do it manually?
>
> Sort of like 'DELETE CASCADE....." which I tried, to no avail. Perusing
> the manual didn't reveal anything....

If you use foreign keys that are set to on DELETE CASCADE then deleting
a referenced row will also delete all of the rows that reference it.