Mailbox-Line: From mange@NO-SPAMORAMA.mjaunet.org  Wed Jul  3 05:07:27 2002
From: =?ISO-8859-1?Q?Magnus_Sj=F6strand?= <mange@NO-SPAMORAMA.mjaunet.org>
Subject: How do I access the current row from a trigger?
Date: Wed, 03 Jul 2002 10:28:14 +0200
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 37
Message-ID: <3D22B59E.2000503@NO-SPAMORAMA.mjaunet.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.1a) Gecko/20020610
To: pgsql-sql@postgresql.org

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