Postgres Pro
Downloads
Home   >   mailing lists

Thread: Trigger Firing Order

Trigger Firing Order

From
Sameer Kumar
Date:
Hi,

Is it possible for me to define the order in which triggers will be fired? 

So far what I have understood from PostgreSQL documentation, in order to fire 2 triggers in sequence (say trigger1 and trigger2) on a table, I need to name them in that way. 
But with certain packaged products who create their own triggers, I won't have control over this. Also, this could be an issue for me if I write business logic in triggers. 

Any better way known to work in PostgreSQL?

Best Regards,
Sameer Kumar | Database Consultant
ASHNIK PTE. LTD.
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533
M : +65 8110 0350 T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz

email patch

This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).

Attachment

Re: Trigger Firing Order

From
David Johnston
Date:
Sameer Kumar wrote
> Hi,
>
> Is it possible for me to define the order in which triggers will be fired?
>
> So far what I have understood from PostgreSQL documentation, in order to
> fire 2 triggers in sequence (say trigger1 and trigger2) on a table, I need
> to name them in that way.
> But with certain packaged products who create their own triggers, I won't
> have control over this. Also, this could be an issue for me if I write
> business logic in triggers.
>
> Any better way known to work in PostgreSQL?

If you have sufficient enough rights on the database you can modify the
triggers in whatever way you deem fit.  There is currently no alternative
way to specify trigger execution order than alphabetically.

I guess such re-naming could introduce problems with applications but that
is unlikely.

Note, too, that you can leave but disable the existing triggers and define
your own triggers with whatever name you require and simply call the same
function as the existing trigger.

Is this a theoretical question or do you actual have this problem?

David J.





--
View this message in context: http://postgresql.1045698.n5.nabble.com/Trigger-Firing-Order-tp5782797p5782800.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: Trigger Firing Order

From
Tom Lane
Date:
Sameer Kumar <sameer.kumar@ashnik.com> writes:
> Is it possible for me to define the order in which triggers will be fired?

Sure: choose their names so that their alphabetical ordering is the
firing order you want.  But I see you knew that.

> But with certain packaged products who create their own triggers, I won't
> have control over this.

I don't have a lot of sympathy for that argument.  If the product is
capable of creating Postgres-compatible triggers at all, it should be
aware that the name is a significant property, and hence provide some
mechanism for selecting a name.  Even if it somehow forgot about the
firing-order property, you can't seriously claim that it's our problem
to cater for combinations of client-side code that each think they can
choose trigger names in a vacuum.  What if they choose the same name?

> Also, this could be an issue for me if I write
> business logic in triggers.

This statement lacks content.  What problem do you foresee, and what
other ordering rule would you like that doesn't work about as well
as the name rule?

            regards, tom lane

Re: Trigger Firing Order

From
Sameer Kumar
Date:
>
>
 But with certain packaged products who create their own triggers, I won't
>> have control over this.
I don't have a lot of sympathy for that argument.  If the product is
capable of creating Postgres-compatible triggers at all, it should be
aware that the name is a significant property, and hence provide some
mechanism for selecting a name.  Even if it somehow forgot about the
firing-order property, you can't seriously claim that it's our problem
to cater for combinations of client-side code that each think they can
choose trigger names in a vacuum.  What if they choose the same name?

Well replication tools which are based on triggers do not let us do that. And if they let me do that it would become too tedious to use them.
If I have a trigger which add primary key to my inserted row ("before trigger"). Now if I plan to create new set of triggers for AUDITING or replication (where either I have no flexibility of choosing a name or the trigger name has to follow a standard), then I need to change all my existing triggers and rename them. Luckily I have ALTER TRIGGER statement to help me (some database don't have that feature), but it could be a substantial work depending on number of trigger I have.


Note, too, that you can leave but disable the existing triggers and define
your own triggers with whatever name you require and simply call the same
function as the existing trigger.

Isn't that bit of a trouble if I have to do that for every trigger?

I guess such re-naming could introduce problems with applications but that
is unlikely.

Actually it can cause issues. e.g. when I want to remove a table from replication set (all the triggers must get dropped). Now if I have modified a trigger/created another with a different name, that table will still be part of set.

I was going to propose to work on developing an additional clause "ORDER n" for CREATE TRIGGER statement. Triggers with lowest order gets called first.
Any two triggers who have same order will get called based on their sorting order of their name.
I can always define my triggers with a huge negative number. Now it does not matter if I add any more triggers in future.

Before proposing this change I wanted to make sure that others in community as well think that this could add value.

Re: Trigger Firing Order

From
Kevin Grittner
Date:
Sameer Kumar <sameer.kumar@ashnik.com> wrote:

> If I have a trigger which add primary key to my inserted row
> ("before trigger"). Now if I plan to create new set of triggers
> for AUDITING or replication (where either I have no flexibility
> of choosing a name or the trigger name has to follow a standard),
> then I need to change all my existing triggers and rename them.

You have auditing or replication triggers that fire as BEFORE
triggers?  What do they do if a subsequent trigger further modifies
the operation before the statement is applied to the database?
(Obviously, all BEFORE triggers fire before the statement is
applied, and all AFTER triggers fire after the statement is
applied, so naming can never cause an AFTER trigger to fire before
a BEFORE trigger.)

> I was going to propose to work on developing an additional clause
> "ORDER n" for CREATE TRIGGER statement. Triggers with lowest
> order gets called first.

I just include a 3 digit number as part of my trigger names.  Why
is that harder than adding a new clause to the CREATE TRIGGER
statement?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Trigger Firing Order

From
Sameer Kumar
Date:


That sounds like a nice suggestion. I guess it could get rid of most of the issues I forsee. I should follow that for my cases too.
I guess we can live without an ORDER clause.
Thanks everyone for helping.