Archiving and copying records

[TESA0_4]

I have developed an application for recording work place risk assessments and
hazards. Once a risk assessment is 'approved for use' the record needs to be
inviolate. If the risk assessment needs to be revised/edited I have a
procedure that update the superseded record with a status of 'Archived' and
creates a copy with a new version number and a status of 'Draft' that can
then be edited and processed through to 'Approved for Use'.
However, a risk assessment is not a single record on a single table. Each
risk assessment may have many hazards (on another table) and each hazard may
have many risk mitigation strategies (on another table) etc.
There is a low level possibility that one user will call for the 'archiving
and copying' of a risk assessment while another user is accessing one of the
'child' hazard or other records of the risk assessment. My concern is that a
conflict will arise that will interupt the archive/copy process midway
through execution. If this happened I can quickly 'clean-up' the situation
without directly editing the tables but that is because I understand the data
structure. Other users are likely to become thoroughly confused.
If possible, I want to avoid forcing the archive/copy process to occur only
when a user has exclusive access to the DB. Can someone offer a strategy for
managing this scenario so that I can be sure the archive and copy process
does not get interupted by a conflict with a another user?
Can I make my archive/copy procedure do a first pass through the records to
be edited/copied to check that no other user is accessing them and then
'lock' the records until the actual archiving/copying is complete? Other
strategies??
Thanks in anticipation of any suggestions!!

 

[Jack Leach]

This sounds very similar to a post not too long ago where the op was advised
to use a single table with a status field rather than trying to copy
information between different tables.

In any case, search the help files for "Transactions". A Transaction allows
you to specifiy a set of tasks to be done, and if they aren't all done as
required, the entire transaction rolls back.

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[TESA0_4]

Thanks Jack. You have put me on the right track. I was not aware of the
Begin, Commit and Rollback Transaction Statements.