Re: Checking for changes in other tables

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