This is a simplified description of the environment and problem.

I have two tables, with a third table that links the two:

A
-----
ID
VALUE

LINK
----------
ID
TABLE_A_FK
TABLE_B_FK
VAL_A
VAL_B

B
-----
ID
VALUE

Various procedures add or update rows in the LINK table. The LINK table
has a trigger which makes sure that SUM(VAL_A) GROUP BY TABLE_A_FK and
SUM(VAL_B) GROUP BY TABLE_B_FK don't exceed VAL_A and VAL_B
respectively.

The problem is that two processes will sometimes both add a row to the
LINK table, causing the trigger rule to be violated. Because the
transactions can't see each others data, of course the trigger doesn't
throw an exception. There is, of course, no problem when the row is
updated by more than one transaction, as an exception is thrown and the
data is safe.

What would the 'best-practice' method be to prevent this? I was
thinking of adding a timestamp field to table A and table B that would
be updated by trigger every time a row was added to LINK. This should
cause an update collision, preventing the data from violating the rule.

Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.