Re: Triggers on columns

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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 trigger bodies. 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 triggers for each event.
http://dev.mysql.com/doc/refman/5.4/en/create-trigger.html

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center