Rikard Bosnjakovic <rikard.bosnjakovic@gmail.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