| Subject | RE: [firebird-support] Some feedback of my 2.5 experience |
|---|---|
| Author | Helen Borrie |
| Post date | 2011-06-03T08:37:38Z |
At 06:03 PM 3/06/2011, Maya Opperman wrote:
By all means, write BEFORE INSERT triggers for *all* of your tables that use generated values as surrogate primary keys...but the code must be this:
IF (NEW.ID IS NULL) THEN
NEW.ID = GEN_ID(GEN_CUSTOMERINCIDENTS_ID,1);
The "1" argument increments the generator by 1 and delivers the completely new value to the caller, which is what you want to guarantee. Never use GEN_ID(GEN_CUSTOMERINCIDENTS_ID,0) to get a NEW value!!
./heLen
>And this on insertion of new support incidents:*No* component should be doing this! What this does is it gets the previous value of the sequence...and since no transaction has a way to know what that value was used for, it is highly suspicious. It looks like either a coding error or a really silly "solution" to a common silly mistake made by Delphi programmers who cut their teeth on Paradox or Access autoincrements.
>
> IF (NEW.ID IS NULL) THEN
> NEW.ID = GEN_ID(GEN_CUSTOMERINCIDENTS_ID,0);
>
>(Which the person who originally wrote the app put in there, which I'm not so sure it's even needed...the FIBPlus component should be handling this AFAIK)
By all means, write BEFORE INSERT triggers for *all* of your tables that use generated values as surrogate primary keys...but the code must be this:
IF (NEW.ID IS NULL) THEN
NEW.ID = GEN_ID(GEN_CUSTOMERINCIDENTS_ID,1);
The "1" argument increments the generator by 1 and delivers the completely new value to the caller, which is what you want to guarantee. Never use GEN_ID(GEN_CUSTOMERINCIDENTS_ID,0) to get a NEW value!!
./heLen