Re: Having more than one constraint trigger on a table

On 10/22/19 8:26 AM, Andreas Joseph Krogh wrote:
> På tirsdag 22. oktober 2019 kl. 17:12:59, skrev Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@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?

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

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

andreas@visena.com

www.visena.com