Triggers fire before commits... and can not be rolled back?!?

[Re_endian_miss]

I know even less about databases than I thought... I always thought
that the idea behind a transaction was that the actual table in
question does NOT get updated until a commit is issued (either
implicitly or explicitly). However, when I create an "after update"
trigger on table X, and then perform an update inside of a transaction,
the trigger fires IMMEDIATELY, even before I issue the commit or
rollback.

Is this a quirk of DB2? Is there a way to provision triggers that fire
only after a committed table change is made?
Thanks in advance for any advice or similar experiences.

[ak_tiredofspam]

>> However, when I create an "after update"
trigger on table X, and then perform an update inside of a transaction,
the trigger fires IMMEDIATELY, even before I issue the commit or
rollback.
.... <<

that is a correct behavior, as specified in ANSI standard.

Having triggers do too much is a typical mistake. Triggers should be
left to handle only simple tasks. Why don't you explain what you need
to accomplish - we might help to find another way to implement it

[Re_endian_miss]

good idea... here's what i'm trying to accomplish:

App commits update to database#1
App gets back result and proceeds with whatever is next
Update to database#1 causes data to be written to either log file or
database#2, completely unbundled and on a different time scale than
database#1

i was trying to avoid having the App do BOTH updates, which is
possible. also, i thought triggers are a good way of centralizing a
change, e.g. trigger on insert to send an email is a nice,
single-location of logic that will execute regardless of if there are
dozens of apps who perform an insert.

given my objectives, do you see any way to use triggers?

as always, thanks in advance for this excellent feedback...

ak************@ yahoo.com wrote:

However, when I create an "after update"

trigger on table X, and then perform an update inside of a

transaction, the trigger fires IMMEDIATELY, even before I issue the commit or
rollback.
... <<

that is a correct behavior, as specified in ANSI standard.

Having triggers do too much is a typical mistake. Triggers should be
left to handle only simple tasks. Why don't you explain what you need
to accomplish - we might help to find another way to implement it

[ak_tiredofspam]

>> Update to database#1 causes data to be written to ... database#2,
completely unbundled and on a different time scale than
database#1
.... <<

that's exactly what replication is used for

[kenfar]

ak wrote:

Update to database#1 causes data to be written to ... database#2,

completely unbundled and on a different time scale than
database#1
... <<
that's exactly what replication is used for

personally, I also like simple asynchronous processes run outside the
database for this scenario: a small python/ruby/kshell/etc script run
by cron every 5 minutes that looks for any of these cases and performs
the appropriate action.

really simple to write & maintain, only downside is that failures won't
be visible to owner of the transaction to report back to you - so a
simple logging & alerting mechanism is also required.

kenfar