Update generator in trigger in firebird
I am using a bulk import for my Firebird database, which might create issues with the generators, as these are not necessarily updated.
The question is, is there a way to update the generator's value directly in the trigger?
I tried following:
begin if (new.ID is null) then begin new.ID = GEN_ID( mygenerator, 1); end else if (new.ID > GEN_ID( mygenerator, 0) ) then begin GEN_ID(mygenerator, new.ID - GEN(ID_mygenerator) ); end end
I also tried ALTER SEQUENCE or SET GENERATOR statements, but none seems accepted within the trigger...
Any suggestion? NOTE: I am using Firebird 2.5.4
I tested this with Firebird 2.5.5 and a trigger generated by Flamerobin (with some minor changes) which works. The important difference seems to be the use of assignment to a local variable:
CREATE TRIGGER WITH_GENERATOR_BI FOR WITH_GENERATOR ACTIVE BEFORE INSERT POSITION 0 AS DECLARE VARIABLE tmp BIGINT; BEGIN IF (NEW.ID IS NULL) THEN NEW.ID = GEN_ID(GEN_WITH_GENERATOR_ID, 1); ELSE BEGIN tmp = GEN_ID(GEN_WITH_GENERATOR_ID, 0); if (tmp < new.ID) then tmp = GEN_ID(GEN_WITH_GENERATOR_ID, new.ID - tmp); END END
It would be possible to modify your existing code to assign to a temporary variable (and using the correct syntax for GEN_ID). However it is advisable to use this code, as your original code (with fixes) would have a race condition that could lead to reversing the generator if in parallel another action changes the generator value so it becomes bigger than NEW.ID. This race condition is avoided in the first code by assigning the value of GEN_ID(GEN_WITH_GENERATOR_ID, 0) to the tmp variable before the check and modification.
The above code could exhibit a race condition that leads to increasing the generator to a higher value than necessary, but I think it would be hard to avoid that. Also keep in mind that when you rollback inserts done with an explicit id, the change of the generator value is permanent and not rolled back.