I want to have a table (a category table in this instance) that will do the
following:
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
This is what I've come up with:
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.
Also, I know I can do this to update the category with three statements but
I'm pretty sure I can do it in one with a subselect but I'm still fuzzy on
how to do that. This is a bad mix of pseudo code and sql.
A = category.parentid from row being deleted
B = select parentid from category where catid=A;
update category set parentid=B where parentid=A;
finish deleting row.
Does any of this make sense?
Alan Young
Programmer/Analyst
IDIGlobal.com