Re: Checking for changes in other tables

two triggers?.

one on eu_loans... and one on persons (if valid eu_loan - cant move...)

2013/4/26 CR Lender <crlender(at)gmail(dot)com>

> I have two tables with countries and persons living in those countries:
>
> create table countries (
> code char(2) not null primary key,
> eu boolean not null
> );
>
> insert into countries values
> ('AR', false),
> ('BE', true),
> ('CH', false),
> ('DE', true);
>
> create table persons (
> name text not null primary key,
> country char(2) not null references countries(code)
> );
>
> insert into persons (name, country) values
> ('Arthur', 'AR'),
> ('Betty', 'BE'),
> ('Charlie', 'CH'),
> ('Diane', 'DE');
>
> Enter a third table for loans that can only be made between persons
> living in EU countries:
>
> create table eu_loans (
> donor text not null references persons(name),
> recipient text not null references persons(name),
> primary key (donor, recipient)
> );
>
> insert into eu_loans (donor, recipient) values
> ('Diane', 'Betty');
>
> I can add a trigger on eu_loans to check if Diane and Betty both live in
> the EU. The problem is how to prevent one of them from moving to a
> non-EU country (if they do, the loan has to be cancelled first). They
> are however allowed to move to other EU countries.
>
> At the moment, this is checked by the application, but not enforced by
> the database. I could add more triggers to the persons table (and
> another one on countries), but that doesn't "feel" right... countries
> and persons are base data and shouldn't need to "know" about other
> tables using their records. Ideally, eu_loans would have a check
> constraint to verify that its contents remain valid.
>
> Is there any way to ensure that all donors and recipients in eu_loans
> are in the EU, without altering the countries and persons tables?
>
> Thanks for any suggestions.
>
> crl
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>