Subject Re: [firebird-support] Record duplication
Author Helen Borrie
At 08:13 PM 30/05/2006, you wrote:
>I know this is a feeble question but I have to ask it.
>I'm moving my office over to Firebird from Ms Access. I'm still having
>to use Access as a front end cos "that's what we're used to". All is
>going well on the trials apart from one DEEPLY irritating problem. They
>are "used to" being able to select, copy and paste records where the
>company and company address details are the same but the contact name
>changes. There's a PK on each record number, so whereas Access
>automatically allocates a new record number, in Firebird they are having
>to manually change the duplicate record number to the next available in
>the PK index, otherwise they get index violation messages. Can anybody
>advise how I could automate the changing of the record number? Is there
>a tweak to the PK trigger I could use, or something else that doesn't
>require techo-dependant but techno-dork-like users to DO anything?

You don't say whether you're using a generator and a before insert
trigger, but I hope so.

Change the trigger to:

if (new.PK_value is null or new.PK_value < 0) then
new.PK_value = gen_id (MyGenerator, 1);

Then have them just type any negative number in the PK field.

This is also safe - avoids the duplication AND steps the generator.