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

If you are using SQL's As Identity then it should generate one for you.

Will your tracking table have the following columns:
Table, Entity, Column, NewValue, User, Timestamp
or some such layout?  And your concern is, if SQL's As Identity clause is 
unique then why would I need Table in the tracking table?

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Reeve Fritchman" <reeve.fritchman@xxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
07/12/2004 09:21 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
cc

Subject
Fastest way to get a unique identifier/tracking column changes






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

 

 

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.