Re: [GENERAL] Trigger Firing Order
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: [GENERAL] Trigger Firing Order
Sameer Kumar sameer.ku...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger Firing Order
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.
Re: [GENERAL] 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger Firing Order
Sameer Kumar sameer.ku...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] trigger firing order
Hugo wrote: Hi, how can I tell in which order are triggered different triggers on the same table, let me explain , I have three triggers for table A, all of then are intended for before insert on the table, in ASA I can tell the Db in which order I want the triggers to fire, is there an equivalent for postgres ?? They're fired in alphabetical order. I tend to prefix my trigger names with numbers for that reason ;) -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] trigger firing order
Hi, how can I tell in which order are triggered different triggers on the same table, let me explain , I have three triggers for table A, all of then are intended for before insert on the table, in ASA I can tell the Db in which order I want the triggers to fire, is there an equivalent for postgres ?? IIRC, I've read some where that they are activated in alphabetical order. Regards, Richard. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] trigger firing order
On Monday 10 April 2006 05:09 pm, Hugo saith: Hi, how can I tell in which order are triggered different triggers on the same table, let me explain , I have three triggers for table A, all of then are intended for before insert on the table, in ASA I can tell the Db in which order I want the triggers to fire, is there an equivalent for postgres ?? thanks for your help Postgres 8.0.4 Windows XP Triggers of the same type fire in alphabetical order; at least, that's how it works is 7.4.6. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] trigger firing order
Terry Lee Tucker wrote: On Monday 10 April 2006 05:09 pm, Hugo saith: Hi, how can I tell in which order are triggered different triggers on the same table, let me explain , I have three triggers for table A, all of then are intended for before insert on the table, in ASA I can tell the Db in which order I want the triggers to fire, is there an equivalent for postgres ?? thanks for your help Postgres 8.0.4 Windows XP Triggers of the same type fire in alphabetical order; at least, that's how it works is 7.4.6. Although, you should really be on at LEAST 7.4.12... :) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Until later, Geoffrey Any society that would give up a little liberty to gain a little security will deserve neither and lose both. - Benjamin Franklin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Trigger firing order
"Alex Bolenok" [EMAIL PROTECTED] writes: peroon=# INSERT INTO t_foo (foo_value) VALUES (2000); NOTICE: fn_foo_ains: Start NOTICE: fn_foo_ains: End NOTICE: fn_bar_ains: Start NOTICE: fn_bar_ains: End Looking at the code, it seems that all AFTER triggers are implicitly handled as DEFERRED triggers, ie, they're queued up and executed at end of statement. This seems wrong to me --- DEFERRED mode is useful, certainly, but it shouldn't be the only form of AFTER trigger. Also, it'd seem to me that DEFERRED mode ought to mean defer till end of transaction, not just end of statement... Jan, any comments here? regards, tom lane
