Thread: Checking for changes in other tables

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

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

On 2013-04-26 12:17, D'Arcy J.M. Cain wrote:
> On Fri, 26 Apr 2013 11:01:28 +0200
> CR Lender <crlender@gmail.com> wrote:

>>     create table countries (
>>         code        char(2)     not null primary key,
>
> Isn't this redundant?  Primary keys are always NOT NULL.

Yes, I forgot to remove the NOT NULL when I adjusted the example.

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

This is just a very reduced example structure (I wouldn't make a
person's first name the primary key, either :-). The actual case doesn't
even involve persons or loans, but it's far too complex to be used as an
example. It took weeks for me to understand how everything in that
database was (supposed to be) connected.

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

I'm not sure I'm following... how would such a constraint look like?

Thanks,
crl

On Fri, Apr 26, 2013 at 11:01:28AM +0200, CR Lender wrote:

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

One way to do this would be to add countries to the eu_loans table so
it looks like this:

create table eu_loans (
    donor text not null,
    donor_country char(2) not null,
    recipient text not null,
    recipient_country char(2) not null,
    primary key(donor, recipient),
    foreign key (donor, donor_country) references persons (name, country) on update cascade,
    foreign key (recipient, recipient_country) references persons (name, country) on update cascade
);

then create an sql function to tell you whether a country is in the eu:

create function country_in_eu (char(2)) returns bool as $$
    select count(*) > 0 from countries where code = $1 and eu = 't'
$$ language 'sql';

and add two constraints to eu_loans:

alter table eu_loans add constraint donor_in_eu check(country_in_eu(donor_country));
alter table eu_loans add constraint recipient_in_eu check(country_in_eu(recipient_country));

This will give an error if someone moves outside the EU (but not if a
country leaves the EU).

It may or may not seem elegant depending on your thinking but it does
have the effect you're looking for. Of course you could set things up
so that you could do an insert to eu_loans specifying just the donor
and recipient names and the system would populate the country fields
for you by looking up in persons, throwing an error if appropriate.

Richard

On 26/04/13 10:01, CR Lender wrote:
> 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.

I think this is more a problem of terminology rather than your current
triggers. Triggers aren't really "part" of a table, but they are
observing it, so it's a sensible place to list them when viewing a
table-definition in psql. There's no reason the trigger function is even
in the same schema as the targetted table.

How would it feel if the syntax was more like the following?

CREATE TRIGGER ... OBSERVING UPDATES ON persons ...

or even

PUBLISH UPDATE,INSERT,DELETE ON persons AS person_changes;
SUBSCRIBE TO person_changes CALLING PROCEDURE ...;

A different "feel", but no difference in behaviour.

--
   Richard Huxton
   Archonet Ltd

Sorry for the late reply, I had to give this some time to digest.
I'm still trying to wrap my head around the concept that triggers don't
"belong" to a table...

On 2013-04-26 17:15, Richard Huxton wrote:
> On 26/04/13 10:01, CR Lender wrote:
> I think this is more a problem of terminology rather than your current
> triggers. Triggers aren't really "part" of a table, but they are
> observing it, so it's a sensible place to list them when viewing a
> table-definition in psql. There's no reason the trigger function is even
> in the same schema as the targetted table.

Okay, that's true for the trigger function (procedure), but not for the
trigger itself. As far as I can tell, triggers aren't directly
adressable, except through their tables. I can have two separate
triggers with the same name (in the same schema) on different tables.

> How would it feel if the syntax was more like the following?
>
> CREATE TRIGGER ... OBSERVING UPDATES ON persons ...
>
> or even
>
> PUBLISH UPDATE,INSERT,DELETE ON persons AS person_changes;
> SUBSCRIBE TO person_changes CALLING PROCEDURE ...;
>
> A different "feel", but no difference in behaviour.

Yes, I see your point; that's how it would look if triggers were
completely separate from their tables, in a pub/sub way. I guess I
wouldn't have a bad feeling about this if I could define them like that.

On the other hand: triggers can't just subscribe to anything, they can
only react to events on a single table; they are automatically and
silently deleted when the table they are observing is dropped; they can
be enabled or disabled via ALTER TABLE, not ALTER TRIGGER.

AFAICS, there's also no \d command in psql to list triggers; they are
only shown when the observed table is inspected with \d.

All of this makes it hard for me to see a trigger as a detached observer
rather than a "behavior" of a table, so to speak.

I need to think about this some more.

Thanks for your help,
crl