Re: Fastest way to get a unique identifier/tracking column changes

[Tom Jedrzejewicz <tomjedrz@xxxxxxxxx>]

Depending on your time frame and budget, you might consider using an
existing third-party database auditing application.  Why re-invent the
wheel?

DataThread from Innovatum comes to mind, although I am sure there are
others.  I have limited experience, but it appears to have the
features you need, including the ability to selectively track changes
based on column values, and to select whether to use triggers or
journals.

Good luck.

On Mon, 12 Jul 2004 10:21:40 -0400, Reeve Fritchman
<reeve.fritchman@xxxxxxxxxx> wrote:
> I'm designing a new system with a requirement for detailed tracking of, and
> inquiry into, column-level changes.  I've decided to build a single file
> with before and after values, etc. for all the tables by using triggers.
> Some of the tables have complex keys (order number/SKU/shipper
> location/consignee location/release number), and I don't want to burden my
> historical tracking file with a nasty key structure to support inquiry into
> the details of the changes.  I'm not going to track added records or
> date-of-last-change timestamps in the tables; the majority of the changes
> will be on a limited number of columns (of the status and date nature).
> 
> My design is to assign every row an "entity number"; the entity number would
> be like a record serial number, would be unique on a system-wide basis, and
> would be the key to the historical tracking table.  When a user wants to see
> the details of the changes to a specific row, the row's entity number would
> allow simple access to the tracking file.  Using SQL's AS IDENTITY with the
> table name could work to provide a key to a specific record.
> 
> The challenge is to determine a way to get the entity number quickly.
> Having a control file is okay but probably limiting performance-wise;
> another possibility is a journaled data area.  Is there a system API
> providing a guaranteed unique sequential number?  Or is there a better
> approach for tracking column-level changes?
> 
> Thanks,
> 
> Reeve
-- 
Tom Jedrzejewicz
tomjedrz@xxxxxxxxx