How do I access the current row from a trigger?

From: Magnus Sjöstrand <mange(at)NO-SPAMORAMA(dot)mjaunet(dot)org>
To: pgsql-sql(at)postgresql(dot)org
Subject: How do I access the current row from a trigger?
Date: 2002-07-03 08:28:14
Message-ID: 3D22B59E.2000503@NO-SPAMORAMA.mjaunet.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi,

I have a table as:
create table sections (
id serial not null primary key,
parent_id references sections(id)
);

there is only one row where the parent_id is NULL, and that is the root
section, all others refers to a section. Now I want to create a trigger,
so that whenever I delete a section I can delete all sections that has
the deleted section as parent, which in turn will run the triggers for
the deleted sections. Thus I will be able to clear an entire branch with
only one SQL command from the client.

consider the content:
id parent_id
0 NULL
1 0
2 1
3 2

and when I run the command
delete from sections where id=1;

I want this to trigger a function which will do
delete from sections where parent_id=( 'id' field in the current row[1] )

which in turn will trigger the same function to do
delete from sections where parent_id=( 'id' field in the current row[2] )

is there any way to do this in PostgreSQL? I couldn't find a way of
refering to the current row in a trigger in the postgresql reference.

Sincerely,
/Magnus Sjöstrand

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message GRIMOIS Eric 2002-07-03 14:48:24 Localization
Previous Message Achilleus Mantzios 2002-07-03 07:54:34 Re: Why doesn't it use indexes?