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.
You can use a trigger, but not w/syntax like this. REFERENCES... ON
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.
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington