I’ ve got the following SQL statement in a Interbase transaction:

Select max(field1) from table1
Into :max
Insert into table1 (field1) values(:max+1)

What parameters of the transaction should I use, to assure the value in field1 to be unique and subsequent, when several transactions are simultaneously running.
For DB access I use Delphi + TIBTransaction.

Hi Amarinov,
My tip is that you can use generators. Here are two sample. First one is using procedure, second one is using trigger. If you are use procedure based version than you can have a return value for example record ID.

Attila


/*Generators*/

CREATE GENERATOR ATID_GEN ;
SET GENERATOR ATID_GEN TO 0;

/*Create table*/

CREATE TABLE TBL_ADR_TYP (
ATID INTEGER NOT NULL,
ATCODE VARCHAR (5),
ATYP VARCHAR (150));

/*Create unique primary key*/

ALTER TABLE TBL_ADR_TYP ADD CONSTRAINT PK_TBL_ADR_TYP PRIMARY KEY (ATID);

/*Create Autoincrement procedure*/

CREATE PROCEDURE ATID_INC
RETURNS (
NEW_VALUE INTEGER)
AS BEGIN NEW_VALUE = GEN_ID(ATID_GEN , 1);
END

/*Option 1A - Autoincrement via stored procedure*/

CREATE PROCEDURE TBL_ADR_TYP_I (
ATCODE VARCHAR (5),
ATYP VARCHAR (150))
AS
BEGIN
INSERT INTO TBL_ADR_TYP (
ATID,
ATCODE,
ATYP)
VALUES (
:GEN_ID(ATID_GEN , 1),
:ATCODE,
:ATYP);
END

/*Option 1B - Autoincrement via stored procedure with return value*/

CREATE PROCEDURE TBL_ADR_TYP_I (
ATCODE VARCHAR (5),
ATYP VARCHAR (150))
RETURNS (
ATID INTEGER)
AS
BEGIN
ATID = GEN_ID(ATID_GEN , 1);
INSERT INTO TBL_ADR_TYP (
ATID,
ATCODE,
ATYP)
VALUES (
:ATID,
:ATCODE,
:ATYP);
END

/*Option 2 - Autoincrement via trigger*/

CREATE PROCEDURE TBL_ADR_TYP_I (
ATCODE VARCHAR (5),
ATYP VARCHAR (150))
AS
BEGIN
INSERT INTO TBL_ADR_TYP (
ATCODE,
ATYP)
VALUES (
:ATCODE,
:ATYP);
END

CREATE TRIGGER ATID_BI_INC FOR TBL_ADR_TYP INACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.ATID IS NULL)
THEN NEW.ATID = GEN_ID(ATID_GEN, 1);
END