Обсуждение: Constraint Trigger's referenced_table
How does a Constraint Trigger react to a referenced table when the constraint is created implementing the FROM clause? Regards, Richard Broersma Jr.
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 ONtable 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 thisfeature 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 onthe 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 triggerson 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 hasrecords 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 designedto 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 implementedin 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 CREATEConstraint Trigger enable this kind of notification to perform cross checking even though there are strictly no pk/fkrelations 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 tablehas 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 haveON UPDATE or ON DELETE actions set. I think I understand now. Thanks for the clarification. Regards, Richard Broersma JR.