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

Answers


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.


Need Your Help

angularjs device back button not working

javascript angularjs modal-dialog addeventlistener

I am trying to create dialog when device back button clicked, searched internet and came up with the coding.

Wildflower CMS is NOT a CakePHP plugin...Baked Simple CMS IS a CakePHP plugin...what's the difference?

php cakephp plugins content-management-system

I just started playing with CakePHP and I found Wildflower CMS . I like the idea and am going to start tinkering with it. I have a question, though.