Re: How can I do this?

> 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();

So the parentid would then just be

parentid int references category ( catid ),

?

> Your function foo() can dig the new value you want out and update this for
> all the related records.

I'm still not clear on how I would get the appropriate data into the
function.

CREATE FUNCTION updatecat ( ??? )
RETURNS int
AS 'A = select parentid from category where catid=<catid to be
deleted--where does this come from?>;
update category set parentid=A where parentid=<catid to be deleted>;'
LANGUAGE 'sql';

How do I get the appropriate info into the query?  Also, I'm fairly certain
I can do that select/update as a subselect but I'm not sure how to go about
doing that.  I'm new to subqueries as well.

> 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.

I want to have a category table that supports sub categories.  Easy enough,
the table I defined in my original post works just fine for that.  But what
I want to happen is that a subcategory will be reassigned automagically to
it's parent category's parent category upon deletion of the parent category.
Ummm ... I'm not sure how to say that any better.

For example, I have the following categorys

catid | parentid | catname | catdesc
========================
0      |                | TOP      | Top level category
1      | 0             | cat1       | cat one
2      | 1             | cat1.1    | cat one:one
3      | 2             | cat1.1.1 | cat one:two:three

delete from category where catid=2;

I want the following to happen automagically.

catid | parentid | catname | catdesc
========================
0      |                | TOP      | Top level category
1      | 0             | cat1       | cat one
3      | 1             | cat1.1.1 | cat one:two

Does that make more sense?

Alan Young
Programmer/Analyst
IDIGlobal.com