> 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