Re: naming triggers for execution

From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
To: PegoraroF10 <marcos(at)f10(dot)com(dot)br>, pgsql-general(at)postgresql(dot)org
Subject: Re: naming triggers for execution
Date: 2019-11-15 15:43:10
Message-ID: 79306433-e971-a80f-be28-2af6cb7314ee@aklaver.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 11/15/19 7:11 AM, PegoraroF10 wrote:
> well, my way of doing auditing is done on replica, so it´s a little different
> on production server I do on before update
>
> IF (tg_op = 'UPDATE') AND (new.* IS DISTINCT FROM old.*) THEN
> new.userauditing = User_ID
> new.datetimeauditing = current_timestamp;
> END IF;
> RETURN new;
>
> Then, on replica server I do
> IF (tg_op = 'INSERT') THEN
> insert into auditingtable .... with insert data
> ELSIF (tg_op = 'UPDATE') AND (new.datetimeauditing IS DISTINCT FROM
> old.datetimeauditing) THEN
> insert into auditingtable .... with old and new data
> ELSIF (tg_op = 'DELETE') THEN
> insert into auditingtable .... with old data
> END IF;
> That trigger on replica is configured to run on replica with ENABLE REPLICA
> TRIGGER
>
> With this approach I´m sure nothing will be audited if nothing was changed
> and additionally all auditing will be done on replica which will frees the
> production server for production and not auditing.
>
> But, independently of my auditing is being different from yours, what do you
> do when you have two triggers using same event on same table.
> Another example I can give you is when you define a PK. Imagine you have a
> function which creates your PK, but another trigger needs that pk value to
> do something. Both are ran before insert but trigger which creates PK needs
> to be the first. How can you sure this happens.

Back to your original post:

"If multiple triggers of the same kind are defined for the same event,
they will be fired in alphabetical order by name."

Use appropriate naming or combine/nest the functions.

>
>
>
> --
> Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2019-11-15 15:54:33 Re: Create array of data from JSONB in PG 9.5
Previous Message Tom Lane 2019-11-15 15:16:59 Re: porting horde to Postgresql 12, dropped pg_attrdef