At 03:19 AM 15/05/2012, Michele Federici wrote:

>Hi,
>
>I'm doing some test with generators. When I've a generator I add a
>trigger as this:
>
>CREATE TRIGGER BI_MYTABLE FOR MYTABLE
>ACTIVE BEFORE INSERT
>POSITION 0
>AS
>BEGIN
> IF (NEW.ID_MYTABLE IS NULL) THEN
> NEW.ID_MYTABLE = GEN_ID(MYTABLE_GEN, 1);
>END
>
>Now, i want that if client adds a record with value ID_MYTABLE > current
>generator value, the generator must be updated to ID_MYTABLE value
>
>CREATE TRIGGER BI_MYTABLE FOR MYTABLE
>ACTIVE BEFORE INSERT
>POSITION 0
>AS
>DECLARE VARIABLE ACTUAL_VALUE BIGINT;
>BEGIN
> IF (NEW.ID_MYTABLE IS NULL) THEN BEGIN
> NEW.ID_MYTABLE = GEN_ID(MYTABLE_GEN, 1);
> END ELSE BEGIN
> ACTUAL_VALUE = GEN_ID(MYTABLE_GEN, 0 );
> if (NEW.ID_MYTABLE > ACTUAL_VALUE) THEN BEGIN
> ACTUAL_VALUE = GEN_ID(MYTABLE_GEN, NEW.ID_MYTABLE - ACTUAL_VALUE );
> END
> END
>END^
>
>Can i have some problem with concurrency?

Yes, for sure. Values for generators come from a transaction context that is independent of any transactions that request them. A transaction simply has no way to know whether the "last value" that it sees is going to be the same when it commits its own work. Just don't even think of employing a trick like this to fool the generator.

As Mark recommended, make applications where users cannot enter their own value or change it - either hide that column or make it read-only once a value has been fetched from the generator.

If you have users entering data outside the control of your application, through tools or private code, then enforce the rule of atomicity by eliminating or excepting any rogue value in an 'update or insert' trigger.

./heLen