Thread: Order of update triggers
Greetings!
I have a table with several triggers. One AFTER UPDATE trigger deletes a record if a field named “result” contains -55. A second AFTER UPDATE trigger writes data from the table into a second table that stores a history of the first table, since the first table’s records are deleted after they are used. The customer has asked that the first trigger be changed so that an update of the results field to -55 results in a new record being added to the history table, and also the record from the main table gets deleted.
Does the order in which those triggers fire matter? Does PostgreSQL guarantee that both triggers will operate on the original data? A quick test on a copy of the production database showed that the history table got the data from the main table after I set the result to -55, and then the record was deleted from the main table. This could be because PostgreSQL has the guarantee I want, or just because the history trigger fired before the delete trigger. Do I need to change anything?
RobR
On Fri, 27 Jan 2012 16:54:35 +0000 Rob Richardson <RDRichardson@rad-con.com> wrote: Hi Bob, triggers are fired by alphabetic order. -- The modern child will answer you back before you've said anything. -- Laurence J. Peter
I have confirmed that in my case, the delete trigger is happening before the update trigger, presumably because it is happeningin alphabetic order as was stated earlier. But since the data being used by the update trigger comes from the 'new'object instead of from the current contents of the table, it does not matter that the underlying record may have alreadybeen deleted. (Of course, all triggers are fired within a single transaction, and inside a transaction, terms like"before", "after" and "already" are effectively meaningless. Right?) RobR
On Fri, 27 Jan 2012 17:45:41 +0000
Rob Richardson <RDRichardson@rad-con.com> wrote:
> (Of course, all triggers are fired within a single transaction,
> and inside a transaction, terms like "before", "after" and
> "already" are effectively meaningless. Right?)
Absolutely not: you have "time stages", even into a single
transaction.
ie: * Table TBA contains a "price" column,
* Trigger TR1 creates an average of "price"
* If you launch TR1 BEFORE (insert), the new value you're about
to insert won't be accounted
* If you trigger TR1 AFTER (insert), it will.
And alphabetic order only matters for the same family (event)
of triggers.
--
Men will fuck mud.
-- Lenny Bruce