Re: Checking for changes in other tables

On Fri, 26 Apr 2013 11:01:28 +0200
CR Lender <crlender(at)gmail(dot)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(at)druid(dot)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(at)Vex(dot)Net, VOIP: sip:darcy(at)Vex(dot)Net