At 08:36 AM 20/05/2006, you wrote:

>Hi Luc and HJ,
>
>
> >It looks like your generator GEN_FATTURE_IDFATTURA gives you back a new
> >value which is always assigned to an existing record.
>
>Not exactly my case ... in effect i import an old access database in
>which there are old value of Primary Key with some old value
>Example
>1
>2
>5
>6
>9
>10
>
>When i insert some new value the
>IF (NEW.IDFATTURA IS NULL) THEN
>NEW.IDFATTURA = GEN_ID(GEN_FATTURE_IDFATTURA, 1);
>END
>
>reach the 1 that just exists and give me the error.
>
>How to solve that ?

You cannot have two independent sources of numbers for your primary
key. You have to decide to use ONE or THE OTHER - not both!


For the import, do ONE of the following things (not both):

Either:

Solution 1:
Abandon the numbers that were used in Access and replace them with
generated numbers. You can only do this if you do not have records
in other tables that depend on those old numbers. Here is the
trigger to do this:

create trigger for_import_only for FATTURA
active before insert as
begin
new.idfattura = gen_id(gen_fatture_idfattura, 1);
end

When the import is done, either set this trigger INACTIVE, or DROP it.

Solution 2:
If you have columns in other tables that depend on the old Access PK
values, then you can use your current trigger but FIRST, set your
generator to a value that is higher than the maximum value of the PK
column in the old database. Let's suppose the highest key value is
12345. Execute this statement in isql:

SET GENERATOR gen_fatture_idfattura TO 12500;
Now, when a NULL is encountered, the key value will not duplicate any
existing key.

>Another question that is linked to the problem above:
>when i delete a record the Primary Key will be deleted too so it's
>natural in that case to broke the autoincremental value of Primary
>key, isn't it ?

No. Generators are not related to the tables that use them in any
way. A generator will just keep generating the next number. That's
the whole idea. As you as a human being doesn't come along and
meddle with a generator, it just generates every higher numbers.

./heLen