Re: Checking for changes in other tables

On Fri, 26 Apr 2013 11:01:28 +0200
CR Lender <crlender@gmail.com> wrote:
> I have two tables with countries and persons living in those
> countries:
>
>     create table countries (
>         code        char(2)     not null primary key,

Isn't this redundant?  Primary keys are always NOT NULL.

> 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)
>     );

Side question - are you really limiting them to one loan each?  Can't a
donor have two active loans with the same recipient?

> 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.

Wouldn't two constraints, one for each of donor and recipient, do the
job?  Moving a person out of the EU would have the same effect as
deleting them.  The constraint would prevent it.

--
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 788 2246     (DoD#0082)    (eNTP)   |  what's for dinner.
IM: darcy@Vex.Net, VOIP: sip:darcy@Vex.Net