Constraint Trigger's referenced_table

Richard Broersma Jr <rabroersma@yahoo.com> writes:

How does a Constraint Trigger react to a referenced table when the constraint is created implementing the FROM clause?

It doesn't. That table OID is just stored for the trigger function
to use if it wants to.

regards, tom lane

--- On Sat, 12/22/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

It doesn't. That table OID is just stored for the
trigger function
to use if it wants to.

I see. I was thinking that the FROM clause of the Constraint Trigger would allow tuple modifications from either the ON table or FROM table to raise the raise the trigger.

So is the purpose of knowing the reference_table OID, is that it helps to generalize the logic of the CONSTRAINT TRIGGERS?

Regards,
Richard Broersma Jr.

Richard Broersma Jr <rabroersma@yahoo.com> writes:

So is the purpose of knowing the reference_table OID, is that it helps to generalize the logic of the CONSTRAINT TRIGGERS?

No, the purpose is to support foreign-key triggers. FK constraints are
implemented via cooperating triggers on the two tables, and each trigger
has to be able to look at the other table.

regards, tom lane

--- On Sat, 12/22/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

No, the purpose is to support foreign-key triggers. FK
constraints are
implemented via cooperating triggers on the two tables, and
each trigger
has to be able to look at the other table.

Sorry Tom, I guess I am still a bit confused about how all of this works and if there are any useful ways I can use this feature to enforce the slightly more complicated temporal foreign-key RI.

Does any documentation exist that I could spend some time reading that explains detail the cooperation between triggers on the two tables?

Regards,
Richard Broersma Jr.

Richard Broersma Jr <rabroersma@yahoo.com> writes:

Does any documentation exist that I could spend some time reading that explains detail the cooperation between triggers on the two tables?

There's always the source code:
src/backend/utils/adt/ri_triggers.c

regards, tom lane

--- On Sat, 12/22/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

There's always the source code:
src/backend/utils/adt/ri_triggers.c

Thanks. I will do my best and give it a try :-)

--- On Sat, 12/22/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

No, the purpose is to support foreign-key triggers. FK constraints are
implemented via cooperating triggers on the two tables, and
each trigger has to be able to look at the other table.

When you say "each trigger has to be able to look" do you mean that each trigger needs to be notified when other table has records that are inserted/updated/deleted?

From what I gathered from the reading of ri_triggers.c, FWIU it seems that this kind of cross table checking is designed to seek out any FKs or PKs in the related table and then execute a validating query that compares the PK to FK.

Can this kind of cross checking still be implemented for temporal relations where foreign keys are not actually implemented in the "referencing" table.

For example I want to enforce the primary-relation's time range to always engulf the time range of the foreign-relation.:
PK timeline:

|---p1---|--p2--|-p3-|p4|---p5---|

FK timeline:
|-f1-|----f2----|--f3--|--f4--|

So if I INSERT/UPDATE/DELETE records to either table, would implementing the "FROM referenced_table" predicated of the CREATE Constraint Trigger enable this kind of notification to perform cross checking even though there are strictly no pk/fk relations between these table?

Regards,
Richard Broersma Jr.

Richard Broersma Jr <rabroersma@yahoo.com> writes:

--- On Sat, 12/22/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

No, the purpose is to support foreign-key triggers. FK constraints are
implemented via cooperating triggers on the two tables, and
each trigger has to be able to look at the other table.

When you say "each trigger has to be able to look" do you mean that each trigger needs to be notified when other table has records that are inserted/updated/deleted?

No, each trigger fires for events in its own table. It then has to go
look at the other table to see if the FK constraint holds (and,
depending on which trigger you are talking about, perhaps make it hold
by changing the other table).

regards, tom lane

--- On Sun, 12/23/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

(and, depending on which trigger you are talking about, perhaps
make it hold by changing the other table).

Okay,

I take it that changing the other table would only apply to tables that were designed with foreign key constraints that have ON UPDATE or ON DELETE actions set.

I think I understand now. Thanks for the clarification.

Regards,
Richard Broersma JR.