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