The Help for the SET TABLEVALIDATE Command does not communicate what happens when a table's integrity is detected as compromised.
I have been particularly interested in its
nLevel setting to 2, which is designed to "
Validate record count when appending or inserting records and writing them to disk.".
I see this setting as the more useful of them all given the need of
nLevel values of 1 or 3 to LOCK the table header in order to process.
My limited testing of the operation of SET TABLEVALIDATE TO 2 has yielded some surprises and I think they are worth sharing. I would be most interested to hear if others obtain similar results.
Most surprising is that I was unable to intercept an error condition using either ON ERROR or TRY...CATCH...FINALLY
when buffering (row or table, optimistic) was in effect.
I had expected these to trigger, but they didn't.
The only way I was able to detect the error condition was to
process the non-TRUE return from TABLEUPDATE()!
In addition, and
regardless of buffering style (or not), there was never an error reported when the record-count inconsistency was only 1 record, indicating strongly that the validation processing is performed
after the INSERT or APPEND is completed.
When the record-count inconsistency was 2 less than expected then an error (#2091) was raised. HOWEVER, in this case it was only when there was
no buffering in effect that the table's final status was legitimate (at least as I see things).
The original table had
RecCount = 5, LogicalSize = 339, PhysicalSize = 339
When the table was (artificially) corrupted to have a record count of 3 and with counts reflected as
RecCount = 3, LogicalSize = 335, PhysicalSize = 339
and then an UNbuffered single INSERT INTO... was executed to add a 2 byte record the resultant table showed values of
RecCount = 4, LogicalSize = 337, PhysicalSize = 339
and the last record (#4) contained the value INSERTed ("6").
However, the same operation done with ROW BUFFERING in effect resulted in
RecCount = 5, LogicalSize = 339, PhysicalSize = 339
and the last record (#5) did NOT contain the value INSERTed but rather the value of the last record that was 'corrupted out'. The expected result is
RecCount = 4, LogicalSize = 337, PhysicalSize = 339
With TABLE BUFFERING in effect the RecCount/LogicalSize/PhysicalSize values were the same as for row buffering
but in this case the last record shown by the LIST command was #4 (as expected) and its field value was "6" as was the value INSERTed.
Admittedly there may be something amiss in my tests, so any comparisons are most welcomed.