On Thu, 21 Dec 2000, Alan Young wrote:
reference a field within the same table (you'll see why later)
have a field automatically changed to another row's field value upon
deletion of the referenced field
create table category (
catid serial primary key,
parentid int references category ( catid ) on delete <?trigger here?>,
catname text unique,
catdesc text
);
I know I need to create a trigger but I'm not sure how to do that. I've
read the docs, but an example would be great for this particular need.
reference a field within the same table (you'll see why later)
have a field automatically changed to another row's field value upon
deletion of the referenced field
create table category (
catid serial primary key,
parentid int references category ( catid ) on delete <?trigger here?>,
catname text unique,
catdesc text
);
I know I need to create a trigger but I'm not sure how to do that. I've
read the docs, but an example would be great for this particular need.
DELETE... only allows you to to do regular referential integrity stuff
(set the field to null, block deletions, etc.), not look up a better value
and put it there.
You want to create a trigger with something like:
CREATE TRIGGER foo BEFORE DELETE ON category FOR EACH ROW EXECUTE
PROCEDURE foo();
Your function foo() can dig the new value you want out and update this for
all the related records.
Can you explain what it is that you want to do? It sounds like you're
building a tree here, and there might be a better strategy overall than
the one you're starting with.
