På torsdag 24. oktober 2019 kl. 16:59:42, skrev Adrian Klaver < adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>>: On 10/22/19 8:26 AM, Andreas Joseph Krogh wrote: > På tirsdag 22. oktober 2019 kl. 17:12:59, skrev Adrian Klaver > <adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>>: > > [snip] > No. > When I sort the triggers I get: > > test=# create table trg_str(fld_1 varchar); > CREATE TABLE > test=# insert into trg_str values ('trigger_1_update_fts'), > ('trigger_2'), ('trigger_3'), ('trigger_1_check_nocycle'); > INSERT 0 4 > test=# select * from trg_test order by fld_1 ; > id | fld_1 > ----+------- > (0 rows) > > test=# select * from trg_str order by fld_1 ; > fld_1 > ------------------------- > trigger_1_check_nocycle > trigger_1_update_fts > trigger_2 > trigger_3 > > Is this how you want them to fire as it does not match what you say > above?: > > (I know they were not /declared/ in that order, but..) > Yes, all "trigger_1_*" are the "actuall triggers triggering the logic", > trigger_2 and trigger_3 are only there as part of the "make > constraint-triggers fire only once"-mechanism, in which the function in > the first trigger is the function performing the actual logic. > So, being I want 2 "logical chunks" to happen I have two > "trigger_1"-triggers (there is no established terminilogy for this > AFAIK), each calling a function performing the logick which is to happen > only once (per row). > > "The first "main" trigger-function is update_company_fts_tf() ... The > second "main" trigger-function is company_parent_no_cycle()" > > It might be easier to understand if sketch out a schematic version of > what you are trying to achieve. > > The point is; I want to functions to be called > - update_company_fts_tf() > - company_parent_no_cycle() > , each only once, as constraint-triggers on the same table. So they are > called by the "level 1 triggers" which must fire first.
To be clear the order they fire relative to each other is not important? Correct, these main functions may fire in any order. > Is it clearer now what I'm trying to achieve? Sort of, though I am still not entirely what the whole process is trying to achieve. What the mix of deferred and un-deferred triggers and 'logical' and housekeeping functions are doing is not clear to me. That is why I suggested a schematic representation of the trigger flow would be helpful. Leave out the fine details and create a flow chart of what you want to happen. Normally, CONSTRAINT TRIGGERs will fire once for each UPDATE. That means, if you do CREATE, the 2 UPDATES, then the trigger(s) will fire 3 times for each row. I'm trying to make these triggers fire only ONCE per row, and at COMMIT (being CONSTRAINT TRIGGER). I'm using the trick mentioned here to achieve this: https://stackoverflow.com/questions/8937203/execute-deferred-trigger-only-once-per-row-in-postgresql#answer-8954058 <https://stackoverflow.com/questions/8937203/execute-deferred-trigger-only-once-per-row-in-postgresql#answer-8954058> But I'm trying to have more than one CONSTRAINT TRIGGER on the same table, each one doing dirfferent things and reacting (triggering) on different columns, and I'm wondering if I can "re-use" the "cleanup-triggers" 2 and 3 as I mentioned, having trigger 2 firing on the sum of all involved COLUMNS (name, duns_number, parent_id) ? -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>
