Thread: Trigger/Function Problem

Trigger/Function Problem

From

kowal@nawigator.pl

Date:

12 February 2001, 18:05:08

                                Table "mp3user"
     Attribute   |    Type     |                     Modifier
--------------+-------------+--------------------------------------------------

       id           | integer     | not null default
nextval('mp3user_id_seq'::text)
      nick         | varchar(20) |

                 Table "mp3user_data"
 Attribute    |    Type        |       Modifier
------------+-------------+----------------------
id               | integer        | not null
imie            | varchar(30)  | not null
nazwisko     | varchar(30)  | not null

I have to table looking like the above (only bigger).

What I want to do I to set a trigger which ON DELETE from "mp3user"
table
will automatically DELETE a row from "mp3user_data" table.

The index in the mp3user is the same as the id from mp3user_data which
should be errased. I've tried looking at the helps in the manual by
about Triggers and Function they kinda suck.

If anyone could solve my problem I would be very, Very grateful.
If possible mail me at: <a
href="mailto:kowal@nawigator.pl">kowal@nawigator.pl</a>

Monsier Kowal

Re: Trigger/Function Problem

From

"Gregory Wood"

Date:

12 February 2001, 18:56:09

This sounds like it might be better handled by foreign keys. You can just
add a constraint to mp3user_data:

FOREIGN KEY (id) REFERENCES mp3user (id) ON DELETE CASCADE. That will
automatically delete matching values in mp3user_data when something is
deleted from mp3user.

Greg

----- Original Message -----
From: <kowal@nawigator.pl>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, February 07, 2001 10:02 AM
Subject: Trigger/Function Problem


>                                 Table "mp3user"
>      Attribute   |    Type     |                     Modifier
> --------------+-------------+---------------------------------------------
-----
>
>        id           | integer     | not null default
> nextval('mp3user_id_seq'::text)
>       nick         | varchar(20) |
>
>                  Table "mp3user_data"
>  Attribute    |    Type        |       Modifier
> ------------+-------------+----------------------
> id               | integer        | not null
> imie            | varchar(30)  | not null
> nazwisko     | varchar(30)  | not null
>
> I have to table looking like the above (only bigger).
>
> What I want to do I to set a trigger which ON DELETE from "mp3user"
> table
> will automatically DELETE a row from "mp3user_data" table.
>
> The index in the mp3user is the same as the id from mp3user_data which
> should be errased. I've tried looking at the helps in the manual by
> about Triggers and Function they kinda suck.
>
> If anyone could solve my problem I would be very, Very grateful.
> If possible mail me at: <a
> href="mailto:kowal@nawigator.pl">kowal@nawigator.pl</a>
>
> Monsier Kowal
>
>

Re: Trigger/Function Problem

From

Peter Eisentraut

Date:

12 February 2001, 19:25:11

kowal@nawigator.pl writes:

>                                 Table "mp3user"
>      Attribute   |    Type     |                     Modifier
> --------------+-------------+--------------------------------------------------
>
>        id           | integer     | not null default
> nextval('mp3user_id_seq'::text)
>       nick         | varchar(20) |
>
>                  Table "mp3user_data"
>  Attribute    |    Type        |       Modifier
> ------------+-------------+----------------------
> id               | integer        | not null
> imie            | varchar(30)  | not null
> nazwisko     | varchar(30)  | not null
>
> I have to table looking like the above (only bigger).
>
> What I want to do I to set a trigger which ON DELETE from "mp3user"
> table
> will automatically DELETE a row from "mp3user_data" table.

Looks like you want to use a foreign key.  Declare your mp3user_data table
something like this:

CREATE TABLE mp3user_data (
  id integer references mp3user on delete cascade,
  ...
);

See http://www.postgresql.org/users-lounge/docs/7.0/user/sql-createtable.htm
for details.

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/