Tom Lane wrote:
> Martin Foster <martin@ethereal-realms.org> writes:
>
>>Is there a way to tell in PL/PgSQL if the delete coming down the line is
>>from a cascade delete? I have a trigger, which when called will throw
>>an error because the row its trying to change is now linked with an item
>>which no longer exists. The foreign key constraint fails since it was
>>removed before underlaying components were.
>
>
> This sounds like you're trying to do manual enforcement of a foreign key
> relationship. Wouldn't it be better to let the standard FK mechanism
> handle it for you?
>
> (But the short answer is no, I don't think you can tell that from
> plpgsql, or even very reasonably from a C trigger.)
>
> regards, tom lane
Not trying to enforce anything, really. Lets do a quick example to
show what I mean, with the following three tables:
Gallery
Layer
Work
In the above a gallery holds layers who in turn hold works. Now when I
remove a gallery it will remove all layers and works associated with it.
Pretty simple and straight forward.
Now what happens is that every time a work is updated or removed it will
make sure that the parent layers and gallery do not use it as a
highlight. Meaning that the trigger upon update/delete of Works will
update Layer and Gallery.
What happens however is that an error is thrown when I delete the
gallery because when it attempts to change the gallery and layer it
can't because they have already been removed.
I figured, if I could tell that this was a CASCADE removal, then I could
change the trigger to take no action on such deletes because there is no
sense in updating rows that will be removed in a short order of time.
The foreign-key constraint is kicked in because the Gallery row is
removed before any row from Work and Layer are.
Does this make sense?
Martin Foster
Creator/Designer Ethereal Realms
martin@ethereal-realms.org