naming triggers for execution

Considering that the auditing needs to be the last, how can I be sure it´ll
ran lastly ?

IMHO, auditing should be done in after- triggers, when that the data in the
records being inserted, updated or deleted can't be changed but you can
still write to the separate auditing tables.
--
Mike Nolan

But suppose we have two triggers, one is called for every table for auditing
purposes, for example. And other for a specific process of that table.
Considering that the auditing needs to be the last, how can I be sure it´ll
ran lastly ?

 
 
May be I am not getting the full picture.  Why can't you name the audit table trigger as Z* 
to make it the last one to be executed.

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.

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

I know it´s possible, but it´s ugly.
When you see a trigger called zaudittable is really strange

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

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.klaver@aklaver.com