On Mon, 14 May 2012 17:19:08 +0200, Michele Federici
<mfederici@...>
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?

Depends on what you mean with 'problem'. There can be context switches
between retrieving, comparing and storing the value, you could be creating
an additional gap of 1 or of (NEW.ID_MYTABLE - ACTUAL_VALUE) (or multiples
of that depending on the number of concurrent executions, and types (with
ID and without ID!). If you don't care about gaps, it is not a problem.

However, in general: use one way and one way only to assign IDs. Maybe it
is even better to have the trigger throw an exception if an ID is provided
by the user instead of generated (might be extreme, but it does make the
'rules' of the db clear).

Mark