C
Chris Strug
Hi,
Following on from a recent post regarding Identity fields with David Portas,
I was discussing a few issues with a collegue and an interesting issue was
raised.
Namely, creating an audit trail in which every row is assigned a unique,
gapless sequential reference. For example, our accountancy package (Sage)
has an audit trail in which every transaction has a numbered reference. Each
number is unique and there are no missing numbers. Obviously some
transactions are cancelled, others may not be assigned in exactly the order
that they were created, etc etc.
I'm just wondering how I would achieve such a thing in SQLS. From talking
with David Portas in my last thread I understand that IDENTITY is not the
way ot achieve this, but I'm curious as to how exactly to achieve this in a
multi-user concurrent environment?
For example, have a unique ID table in which numbers are assigned as they
are required - although I imagine that this would require extensive use of
transactions and locking....
Alternatively would be to have an ON INSERT trigger which simply assigns the
last row reference plus one to the reference field?
Any pointers / sites / etc?
I appreciate that this is a big vague but it's something that I can see
being very useful in the near future.
Any and all advice is gratefully received.
Regards
Chris.
Following on from a recent post regarding Identity fields with David Portas,
I was discussing a few issues with a collegue and an interesting issue was
raised.
Namely, creating an audit trail in which every row is assigned a unique,
gapless sequential reference. For example, our accountancy package (Sage)
has an audit trail in which every transaction has a numbered reference. Each
number is unique and there are no missing numbers. Obviously some
transactions are cancelled, others may not be assigned in exactly the order
that they were created, etc etc.
I'm just wondering how I would achieve such a thing in SQLS. From talking
with David Portas in my last thread I understand that IDENTITY is not the
way ot achieve this, but I'm curious as to how exactly to achieve this in a
multi-user concurrent environment?
For example, have a unique ID table in which numbers are assigned as they
are required - although I imagine that this would require extensive use of
transactions and locking....
Alternatively would be to have an ON INSERT trigger which simply assigns the
last row reference plus one to the reference field?
Any pointers / sites / etc?
I appreciate that this is a big vague but it's something that I can see
being very useful in the near future.
Any and all advice is gratefully received.
Regards
Chris.