Stored procedure question - Interbase NLM. 
Author Message
 Stored procedure question - Interbase NLM.

Hi,
We are using Interbase 4 for Netware (NLM).  For one of our database
we have created triggers for every table which generate a unique
integer (using gen_id( ) ) and stores it in a field of the table
before inserting a new record.  Here is the Interbase code for the
trigger.

CREATE TRIGGER TDEPARTMENT FOR DEPARTMENT                      
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
 DEPARTMENT.DEPARTMENT_ID = gen_id(GEN_DEPARTMENTID, 1);
END

The trigger works great!  We are using TQuery objects, not TTable
objects.  My question is, how can my delphi app get the value of
GEN_DEPARTMENTID after the insert has completed.  To phrase another
way, once I Post (after Insert), how can I get that record back?

I cannot simply retrieve the record with the greatest value of
DEPARTMENT_ID (another user record may have been inserted between my
insert and my checking for the greatest value of DEPARTMENT_ID).

Any ideas?  Thanks!

martin.

+-----------------------------------------------------------+
  Martin Bradley



Tue, 26 Jan 1999 03:00:00 GMT  
 Stored procedure question - Interbase NLM.

Quote:

> Hi,

> CREATE TRIGGER TDEPARTMENT FOR DEPARTMENT
> ACTIVE BEFORE INSERT POSITION 0
> AS
> BEGIN
>  DEPARTMENT.DEPARTMENT_ID = gen_id(GEN_DEPARTMENTID, 1);
> END

> The trigger works great!  We are using TQuery objects, not TTable
> objects.  My question is, how can my delphi app get the value of
> GEN_DEPARTMENTID after the insert has completed.  To phrase another
> way, once I Post (after Insert), how can I get that record back?

> I cannot simply retrieve the record with the greatest value of
> DEPARTMENT_ID (another user record may have been inserted between my
> insert and my checking for the greatest value of DEPARTMENT_ID).

> Any ideas?  Thanks!

> martin.

> +-----------------------------------------------------------+
>   Martin Bradley


Create a stored procedure that retrieves the next value from the
generator. In your ttable or tquery beforepost method, go out to a
tstored proc to retrieve the value from the generator and put it into the
 appropriate field.

Don't forget to modify your trigger to only insert a new value from the
generator if the key value coming to it is null.

hope this helps

--

Robert Schieck
-----------------------------------------------------------------------
MER Systems Inc.

Client/Server Development and Training using Delphi,  InterBase and
Oracle

InterBase page  http://www.mers.com/interbase.html
udflib page     http://www.mers.com/product.html
TDUG page       http://www.mers.com/tdug.html      -Toronto Delphi Users
Group



Wed, 27 Jan 1999 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. NLM Interbase Question

2. Live resultsets from Interbase stored procedure?

3. BLOB FROM DELPHI TO INTERBASE STORED PROCEDURES

4. Interbase with views or stored procedures.

5. Creating Interbase Stored Procedure

6. Local Interbase Server - Stored Procedures ?

7. Interbase - stored procedures

8. Interbase with views or stored procedures.

9. Dynamic WHERE clause in Stored Procedures within INTERBASE ?

10. Granting Interbase privledges from within a stored procedure

11. Interbase Stored Procedures

12. Problem with Interbase Stored Procedure

 

 
Powered by phpBB® Forum Software