Re: Foreign Key normalization question

On Tue 02 Sep 2008 05:35:25 PM EDT, D. Dante Lorenso wrote:
> You could add a trigger to your product_location table that just
> double-checked the customers matched or prevents the insert/update. A
> PL/PGSQL function like this might help:
>
> ---------- 8< -------------------- 8< ----------
>
> DECLARE
> is_ok BOOLEAN;
> BEGIN
> SELECT p.customer_id = l.customer_id
> INTO is_ok
> FROM product p, location l
> WHERE p.product_id = NEW.product_id
> AND l.location_id = NEW.location_id;
>
> -- didnt find the product and location ... weird
> IF NOT FOUND THEN
> RETURN NULL;
> END;
>
> -- product customer matches the location customer
> IF is_ok = TRUE THEN
> RETURN NEW;
> END;
>
> -- product and location customers did NOT match, reject changes
> RETURN NULL;
> END;
> ---------- 8< -------------------- 8< ----------
>
> Disclaimer: I have no idea if that code works. I just whipped it up now
> without testing it. That might do your checks without having to add
> columns to tables you don't want to add.

Thanks! This is what I was looking for. Although I got a few syntax
errors in postgreSQL 8.3 until I changed a few END; statements to END
IF;

Also, I had to put:

create or replace function check_customer ()
returns trigger $$

at the top of this, and

$$ language 'plpgsql';

at the bottom. I'm a novice at writing triggers, and this is really
useful.

Thanks again.

Matt