Tom Lane <tgl@sss.pgh.pa.us> wrote:
> exactly how, and when, are you determining whether a column has
> been "modified"? I can't count the number of times somebody
> has proposed simplistic and incorrect solutions to that.
> Usually they forget about BEFORE triggers changing the row.
There are some approaches:
1. Just check conditions in alphabetical order. Ignore subsequent modifications after the conditions are examined.
2. Recheck conditions if NEW values are modified, but triggers that have been fired already are not executed twice.
3. Column triggers are called after non-conditional UPDATE triggers and column triggers cannot modify NEW values.
I like approach 2. because it is the most user-friendly. There is a
possibility that another trigger changes NEW values to "unmodified"
state after some conditional triggers are executed, but it could be
admissible. The approach 3. seems to be the most strict, but hard to
use because of the restriction.
----
Just for reference:
- Oracle Database: They support multiple triggers and UPDATE OF and WHEN clause and can modify NEW values in
triggerbodies. So they must have same problems discussing here -- but I cannot find how they work around it...
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/create_trigger.htm#i2064026
- MySQL: They can modify NEW values, but no problem because they don't support UPDATE OF, WHEN clause, nor multiple
triggersfor each event. http://dev.mysql.com/doc/refman/5.4/en/create-trigger.html
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
