Re: Having more than one constraint trigger on a table - Mailing list pgsql-general

From Adrian Klaver
Subject Re: Having more than one constraint trigger on a table
Date
Msg-id 33ad2642-ae94-29f6-cbc6-0122894ddf26@aklaver.com
Whole thread Raw
In response to Re: Having more than one constraint trigger on a table  (Andreas Joseph Krogh <andreas@visena.com>)
Responses Re: Having more than one constraint trigger on a table
List pgsql-general
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.

> --
> Andreas Joseph Krogh


-- 
Adrian Klaver
adrian.klaver@aklaver.com



pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: LocalTransactionId vs txid_current
Next
From: Jeff Lanzarotta
Date:
Subject: SQL Error [0A000]: ERROR: nondeterministic collations are notsupported for LIKE