Skip site navigation (1) Skip section navigation (2)

Re: Help with trigger

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Rikard Bosnjakovic <rikard(dot)bosnjakovic(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Help with trigger
Date: 2010-08-15 05:21:42
Message-ID: 10434.1281849702@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
Rikard Bosnjakovic <rikard(dot)bosnjakovic(at)gmail(dot)com> writes:
> I have two small tables:

> dimensions (id, height, width, depth, weight);
> components (id, dimension references dimensions(id));

> When dimensions are changed, a new row is inserted in dimensions-table
> and the id is updated in the components-table. This means there can be
> several dimension-ids that are "orphans" (not referenced by
> components). Instead of purging them manually, I read about triggers
> and tried to use them.

> I did this:

> ecdb=> CREATE OR REPLACE FUNCTION dimension_purge() RETURNS "trigger"
> AS 'BEGIN DELETE FROM dimensions WHERE id NOT IN (SELECT dimensions
> FROM components WHERE dimensions IS NOT NULL); END;' LANGUAGE
> 'plpgsql';
> CREATE FUNCTION
> ecdb=> CREATE TRIGGER dimensions_trigger AFTER INSERT OR UPDATE OR
> DELETE ON dimensions EXECUTE PROCEDURE dimension_purge();
> CREATE TRIGGER

This is a recursive trigger: it fires a DELETE on dimensions, which
causes the trigger itself to be invoked again, etc etc.  Because it's a
statement-level trigger, the fact that the inner invocations don't
actually find anything to delete doesn't stop the recursion.  A DELETE
was executed, so the trigger's supposed to be fired.

From your verbal description I wonder if you shouldn't have the trigger
on components instead.

Also, have you looked into using a foreign key with an ON CASCADE DELETE
action?  That might not have the semantics you want, but if it can be
made to work it beats the heck out of debugging your own triggers ...

			regards, tom lane

In response to

Responses

pgsql-novice by date

Next:From: Rikard BosnjakovicDate: 2010-08-15 08:19:54
Subject: Re: Help with trigger
Previous:From: Rikard BosnjakovicDate: 2010-08-14 21:30:03
Subject: Help with trigger

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group