Fastest way to get a unique identifier/tracking column changes

["Reeve Fritchman" <reeve.fritchman@xxxxxxxxxx>]

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