I found there is a separate trigger for PeopleID as follows;


CREATE TRIGGER CREATEPEOPLEID FOR PEOPLE
ACTIVE BEFORE INSERT POSITION 0
AS BEGIN
NEW.ID = GEN_ID(PEOPLEIDGEN, 1);
END ^

however, the code apparently referencing this trigger is the SQL Insert
statement which I had previously written: SQL.Add('Insert into People (ID)
values (0)');

So, is this still causing the error in the same way thought previously? (I
presume it works in a multi -transactional envirnoment ?)

On 3/14/06, Adam <s3057043@...> wrote:
>
> --- In firebird-support@yahoogroups.com, "Gaurav Sood"
> <sood.gaurav@...> wrote:
> >
> > Hi Helen
> >
> > I've run through the program with the debugger and found that once we
> > try to make a new person with the GUI, it calls the Person.pas file
> > OnShow routine
> >
> > procedure TPersonDlg.FormShow(Sender: TObject);
> > var
> > personType: TPersonTypeEnum;
> >
> > begin
> >
> > { Check for new Person. }
> > if Person < 1 then
> > begin
> > *** Person := NewPerson;
> > Status := pdNew;
> > end
> > else
> > Status := pdEdit;
> >
> >
> > The *** Person := NewPerson; is an internal private function that does
> > the below:
> >
> > function TPersonDlg.NewPerson: TPersonID;
> > begin
> > with ApplicationForm.ScratchQuery do
> > begin
> > { Create a new record in People. }
> > Close;
> > SQL.Clear;
> > *** SQL.Add('Insert into People (ID) values (0)');
> > ExecSQL;
> >
> > { Get ID for new Person. }
> > Close;
> > SQL.Clear;
> > SQL.Add('Select max(ID) from People');
>
> This is a really dumb way of assigning a new ID.
>
> 1) It is slow unless you have a descending index on the PK field
>
> but more importantly
>
> 2) It does NOT work in a multi-transaction environment. Each
> transaction has an isolated view of the database. If I called this
> silly procedure and it gave me ID 100, which I then inserted but have
> not yet committed, then you call the procedure, you will also be given
> ID 100 to use, and you will get the error you received before.
>
> In Firebird, use generators to assign numbers that are guaranteed to
> be unique.
>
> Some initial SQL to create and initialise the generator
>
> CREATE GENERATOR GEN_PEOPLEID;
> SET GENERATOR GEN_PEOPLEID TO 100;
> (change 100 to the maximum value currently allocated)
>
> And change the query to:
>
> SQL.Add('Select gen_id(GEN_PEOPLEID,1) from RDB$DATABASE');
>
> This will then ALWAYS give you a unique number for your person ID.
>
> Adam
>
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>


--



Analyst
B. Engineering, B. IT
A.N.U (2004)

M: +61 401 409 620
H: +61 3 9376 8580


"In life, always do right. This will gratify some people and astonish the
rest."

� Mark Twain


[Non-text portions of this message have been removed]