Re: Referential Integrity problem

On 19 Mar 2003, James Gregory wrote:

> Ok, so, it seems to me that what I need to do is create trigger
> functions to implement this check. They will basically consist of a
> check to maintain referential integrity with this inherited structure,
> and throw an exception if the proposed modification would break that.
> Then, assuming all the checks passed and no exception was thrown it
> would simply return the new row.

> The script sounds like a bad idea, just from a maintenance standpoint.
> So, is there a way to infer what I need to know from within a trigger?
> Alternatively, can I specify the table name as a parameter, and use that
> to do the system table queries?

You can give arguments at CREATE TRIGGER time and do stuff based on the
arguments from there.  You'll need to be careful to make sure all of the
concurrency and modification of the pk side of the constraint works
properly.

As a note, you'll need triggers on saleable_item and product to deal with
modifications to the pk-side of the constraint.  The one on saleable_item
isn't strictly necessary since you don't plan to modify it, but it's
probably good from a consistancy stanpoint.  You'll also need triggers on
any other saleable_item subclasses.