--- In firebird-support@yahoogroups.com, "umarko4life"
<umarko4life@...> wrote:

>
> I have a NEXT_NO table
> 1. Customer 1005
> 2. Deals 1
> 6. Item 5005
>
> from the item form at the time of update I manage to insert the record
> into the item table with the correct no from the NEXT_NO table which
> is 5005 but I need to also update the NEXT_NO table for the item field
> by one. This is not happening please help with an actual example
>

Are you using this to generate your primary key values?

If so, this is a bad approach. If multiple users are connected, the
numbers are not reliable. Firebird has a feature called a generator
which is something that gives you a guaranteed* unique number (* as
long as you don't reset it or move it backwards manually).

The following line will create a generator which could be used for the
customer number.

CREATE GENERATOR GEN_CUSTOMERNO;

GEN_ID(GEN_CUSTOMERNO, 1) is a function that increments the value of
the generator and returns the number. This is multi-user safe!

You can run it as a standalone query:

SELECT GEN_ID(GEN_CUSTOMERNO, 1) AS NEXT_NO
FROM RDB$DATABASE

Or it can be placed directly into a query:

INSERT INTO CUSTOMERS (ID, NAME) VALUES (GEN_ID(GEN_CUSTOMERNO, 1),
'Adam');

Or it can be called from within triggers or stored procedures if you
wanted to emulate an auto-increment field.

SET TERM ^ ;

CREATE TRIGGER TABLEA_BI FOR TABLEA
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.ID IS NULL) THEN
BEGIN
NEW.ID = GEN_ID(GEN_CUSTOMERNO, 1);
END
END
^

SET TERM ; ^

Do not worry if the generated value is never used, or the transaction
is rolled back. Do not try to re-use the numbers or you will get
yourself tangled.

Generators get around the isolation level limitations that your
approach would have.

Adam