Log Data Changes in Oracle HR

The Problem

We needed to build interfaces from Oracle HR to several other systems. To help with this we needed to track changes made.

The Solution

The solution I found had a fatal flaw. We've turned on Oracle Auditing instead.

My bright idea was to add a trigger to the tables to record what we needed when the table was written to. This turned out to be a flawed approach, we think because Oracle does a "test" write to the database and rolls it back as a stage in the validation of the transaction. Tom Kyte has written a good article on why you should avoid triggers: I wish I'd read it earlier!