Insert is occurring, or you wouldn't get a PK violation error. Check your generator's value against
the max() in that table, you may be 1 behind or somesuch. Generators don't guarantee you'll always
be able to insert, they only provide a tool which generally helps. If someone inserts data without
using the generator, you'll need to fix it manually.

Also, I recommend you not perform update-or-insert with an id that is not-null if you intend to
insert, unless it's because you already know what the new PK should be; if your table ever contains
a row with id=0, with your current solution, it'll keep getting overwritten, which is not what you
want. (Update-or-insert will notice that the row exists and perform an update immediately,
bypassing your trigger!) If that's not possible, at least put a constraint on that PK column
requiring id != 0.

---------- Original Message -----------
From: "calou_3324" <calou_3324@...>
To: firebird-support@yahoogroups.com
Sent: Wed, 13 Aug 2008 15:22:03 -0000
Subject: [firebird-support] Re: problem with insert or update

> Hello,
> I have done just an insert and i have the same problem (with an
> update it is good)
> SQL> insert into CODECOMPTA (code_compta) values('test');
> Statement failed, SQLCODE = -803
> violation of PRIMARY or UNIQUE KEY constraint "PK_CODECOMPTA" on
> table "CODECOMPTA"
> i have done a gbak -b and -c and it is the same thing
>
> Thank you for help
>
> Regards
>
> --- In firebird-support@yahoogroups.com, "calou_3324"
> <calou_3324@...> wrote:
> >
> > Hello,
> >
> > Here is the declaration of a table:
> >
> > CREATE TABLE CODECOMPTA
> > (
> > ID INTEGER NOT NULL,
> > TYPE_DEPENSE CHAR( 50) COLLATE FR_FR,
> > CODE_COMPTA CHAR( 30) COLLATE FR_FR,
> > TYPE_MONTANT CHAR( 4) COLLATE FR_FR,
> > SOCIETE CHAR( 20) COLLATE FR_FR,
> > CONSTRAINT PK_CODECOMPTA PRIMARY KEY (ID)
> > );
> >
> >
> > SET TERM ^^ ;
> > CREATE TRIGGER CODECOMPTA_ID FOR CODECOMPTA ACTIVE BEFORE INSERT
> > POSITION 0 AS
> > begin
> > if ( (new.ID is null) or (new.ID = 0) )
> > then new.ID = gen_id(CODECOMPTA_GEN, 1);
> > end
> > ^^
> > SET TERM ; ^^
> >
> > when i do this sql :
> > update or insert into CODECOMPTA
> > (ID,TYPE_DEPENSE,CODE_COMPTA,TYPE_MONTANT,SOCIETE)values
> > ('0','test','test','HT','VALOREM') returning(id)
> > i have this error :
> > violation of PRIMARY or UNIQUE KEY constraint "PK_CODECOMPTA" on
> > table "CODECOMPTA"
> >
> > I don't understand why because if id=0 then the insert should occur?
> >
> > Thank you for help
> >
> > Regards
> >
>
>

------- End of Original Message -------