Thread: naming triggers for execution

On create trigger page of the doc:
If multiple triggers of the same kind are defined for the same event, they
will be fired in alphabetical order by name.
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 ?

Would be cool if we could define position for triggers, or like SQL Server
which defines first and last position of it. But as we cannot use this
thing, what is the way you use.




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

> 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