Subject Re: Triggers suitability
Author Adam
> Thanks for the information. Is it possible to retrieve
this 'record
> version' value?

No, it just has the transaction that created it, and this information
is used to determine whether you can see it or not.

> I was thinking if it could be used to detect changes
> to records (related to my question on avoiding pessimistic

There are two schools of thought here, each with valid points.

1. Pessimistic Blocking is bad

The optimists approach is that too often, a user is blocked from
making a change, even when they two updates could easily be merged.
The way this is normally implemented is by putting a field in the
table, call it UpdateNumber.

You query in this updatenumber with the rest of the data from the

You create a before update trigger that does this



If you sucessfully update the record, the update number will be
incremented. If someone else was also editing the record, they would
pass in the older value of the update number, and the before update
trigger would throw an exception.

If you wanted to go further down this path, you could use Events to
notify the second user that a change has been made to that record.

If they can not update the record, then you will need to come up with
a mechanism to synchonise them, or tell the second user bad luck (in
which case they are not happy).

2. Pessimistic locking is sometimes good.

The idea here is that the customer would rather be prevented from
making a change in the first place than to spend 10 minutes making a
change to then be told they can't make it anymore.

A gate record is the easiest way to do this. You can use a dummy
update statement (but watch for triggers that might fire
unexpectedly) or use a select with lock (FB 1.5 release notes)

The problems you will need to resolve are:

* Who is doing the other edit? Sometimes that is all they need to
know, oh ok, Adam must be fixing that, good.

* What if Adam opens the edit screen and goes off to lunch? This is a
design decision, you could create a timer if you like.

In general, I would recommend an optimistic method when the data is
coming from an automated process. These processes are generally
pretty quick, so large performance benefits can result from not
having to wait around. They also don't get angry with you for wasting
their time.

Where people are involved, for simple cases where very little work is
lost if they aren't allowed to save (typing in a date etc) and their
is a relatively low risk of encountering a block, optimistic is
perfectly OK. Otherwise, stick with the pessimistic lock.