Trigger and Recursive Relation ?

From: "Greg Steele" <gsteele(at)apt-cafm(dot)com>
To: "Postgres Novice" <pgsql-novice(at)postgresql(dot)org>
Subject: Trigger and Recursive Relation ?
Date: 2006-08-01 19:31:15
Message-ID: HCEHKEGAHIFECHKKGAHKAEIOCBAA.gsteele@apt-cafm.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-novice

Hi,
I'm a Postgres newbie trying to figure out a trigger problem. I have a
table with a recursive relationship, and I'm trying to create a trigger that
will maintain the parent child relationship when a record is deleted. For
example, I have records where record 0 references null, record 1 references
record 0, record 2 references record1, and so on. I created a trigger that
maintains the relationship after a deletion. For example, if I delete
record 1 in the above example, record 2 will now point to record 0 (record
1's parent). The scenario works fine when I individually delete records,
but when I try to delete a set of records at once, only 1/2 of the records
are deleted. Probably something simple, but I can't figure out what's
happening. Here's a simplified example of what I am try to do...Please
help! Thanks

Regards,
Greg Steele

CREATE TABLE recursive(
id int PRIMARY KEY,
parent int,
FOREIGN KEY (parent) REFERENCES recursive ON DELETE CASCADE
);

CREATE OR REPLACE FUNCTION delete_on_recursive_trigger_fx() RETURNS trigger
AS
$$
BEGIN

UPDATE recursive
SET parent = OLD.parent
WHERE parent = OLD.id;

RETURN OLD;
END;
$$
Language 'plpgsql';

CREATE TRIGGER delete_on_recursive_trigger
BEFORE DELETE ON recursive
FOR EACH ROW
EXECUTE PROCEDURE delete_on_recursive_trigger_fx();

INSERT INTO recursive(id, parent) values(1, null);
INSERT INTO recursive(id, parent) values(2, 1);
INSERT INTO recursive(id, parent) values(3, 2);
INSERT INTO recursive(id, parent) values(4, 3);

--only 1/2 of the records are deleted!
DELETE FROM recursive;

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Nikolay Samokhvalov 2006-08-01 20:26:25 Re: Trigger and Recursive Relation ?
Previous Message PFudd 2006-08-01 16:54:03 BUG #2560: Web page documentation hard to use

Browse pgsql-novice by date

  From Date Subject
Next Message Nikolay Samokhvalov 2006-08-01 19:58:22 Re: GRANT role TO PUBLIC- not working?
Previous Message Richard Broersma Jr 2006-08-01 19:20:07 Re: GRANT role TO PUBLIC- not working?