Hi All
Are we from Clarion World doing it correctly?
Greetings
JVZ

From NewsServer: discuss.softvelocity.com NewsGroup: comp.lang.clarion
Hi
A stored procedure and trigger is one way. The trigger is made to
only inc the autono of null. The sp bumps the auto no in Firebird and
returns the result and you should have the parent id and for the child

sp
/* Stored procedure for returning the auto inc on a table. Use for
getting the parent new record id for the children
The trigger for the parent must be the one that does not call the
generator unless the id is null
This is a select procedure in Firebird and it operates a little
different than an executable procedure
If called by SELECT * FROM SP Name
the SUSPEND IS REQUIRED so see below
These names could excede the 31 max - so be warned
The generator name is the same name given to the generator on the
Primary key on the table
so the generator needs to be present
*/


SET TERM !! ;
CREATE PROCEDURE whatever
RETURNS (AUTOINCNO INTEGER) AS
BEGIN
AUTOINCNO = GEN_ID(column, 1);
SUSPEND;
END !!
SET TERM ; !!


/* Triggers and generator names using a null condition
This generator is for the stored procedure for returning the auto
inc on a table.
Use for getting the parent new record id for the children
The trigger for the parent must be the one that does not call the
generator unless the id is null
*/

CREATE GENERATOR whatever
SET TERM !! ;
CREATE TRIGGER whatever FOR FileName
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF new.column IS NULL THEN new.column = GEN_ID(column, 1);
END !!
SET TERM ; !!
Jimmy Rogers



On 25 Mar 2004 11:30:02 -0500, "Robert Johnson"
<robert@...> wrote:

>Hi Kelvin. I really appreciate your comments and insight regarding the use
>of Firebird. I am wondering how do you handle autonumbering for
>parent/child records like an invoice situation? I know that a generator

and

>trigger is used to autonumber a table field but is there a way to get this
>value before the record is saved? If not, then do you use a seperate table
>to store the value of the last invoice and increment by one or something
>like that?
>
>Regards,
>
>Robert
>
>

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.648 / Virus Database: 415 - Release Date: 2004-03-31


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