Обсуждение: Trigger Firing Order

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.

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

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

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.