--- In firebird-support@yahoogroups.com, "Rick Debay" <rdebay@...> wrote:

>
> 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.

Wouldn't VAL_A be included in SUM(VAL_A), so other records must be 0
or negative to not break this rule? Or do you mean SUM(Link.VAL_A)
must be less than A.VALUE?

>
> 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.

The only way you can really do this safely is to serialise the summing
of the link table, otherwise transaction isolation will catch you out
(you won't see uncommitted records in the link table, so you won't
really know if your rule has been broken.

>
> 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.
>

That would be one way to do it. You could also perform a dummy update like

update a set value = value where id = ?;

Or use a select with lock.

Adam