Firebird transactions with Firedac

[Alberto Fornés 22]

Hello, I have a Firebird table (ORDEN) where I need to have a unique numbering in a field depending on the date. In the before insert of the table I define a trigger that basically does this:

 

SELECT MAX(NUMBER) FROM ORDEN WHERE FECHAINT >= :FECHA INTO :NUM;
IF (:NUM IS NULL) THEN NUM = 0;
NUM = :NUM + 1;
NEW.NUMBER = :NUM;

Sometimes numbers are repeated in this table, so I deduce that something is wrong in the handling of transactions to cause this error. I work with Firedac, what are the properties of the connection or the write and read transactions supposed to be to avoid this problem?, thanks

[Hans J. Ellingsgaard 21]

Is there a reason why you can‘t use generators?

[Stano 143]

I don't think logic anymore. What happens if you have the same dates? Are the dates changing?
The date itself is a unique number. Isn't that enough for you?

[Anders Melander 1782]

2 hours ago, Alberto Fornés said:

I deduce that something is wrong in the handling of transactions to cause this error.

Wrong conclusion. What is more likely? That there is something wrong with transactions or that there is something wrong with what you're doing?

 

Your method is flawed unless you can guarantee that there will never be more than one transaction active against the database at a time.

Consider this:

• A: Start transaction
• A: select max(NUMBER) returns 1
• B: Start transaction
• A: Post new row with NUMBER=2
• A: Commit
• B: select max(NUMBER) returns [...drumroll..] 1
• B: Post new row with NUMBER=2
• B: Commit

Like Hans suggested, use a generator. That's what they're there for.

[Alberto Fornés 22]

3 hours ago, Hans J. Ellingsgaard said:

Is there a reason why you can‘t use generators?

Really is that there was no reason not to use it, finally it is what I have done and as Anders suggested it solves the problem. Thanks

3 hours ago, Stano said:

I don't think logic anymore. What happens if you have the same dates? Are the dates changing?
The date itself is a unique number. Isn't that enough for you?

In this case the date did not work because I need also assign a code from that number.

2 hours ago, Anders Melander said:

Wrong conclusion. What is more likely? That there is something wrong with transactions or that there is something wrong with what you're doing?

 

Your method is flawed unless you can guarantee that there will never be more than one transaction active against the database at a time.

Consider this:

• A: Start transaction
• A: select max(NUMBER) returns 1
• B: Start transaction
• A: Post new row with NUMBER=2
• A: Commit
• B: select max(NUMBER) returns [...drumroll..] 1
• B: Post new row with NUMBER=2
• B: Commit

Like Hans suggested, use a generator. That's what they're there for.

 Thanks Anders, now works without repetitions using a generator.

[Ruslan 5]

SET SQL DIALECT 3;

CREATE GENERATOR GEN_RECORD_NUMBER;

SET TERM ^ ;

CREATE OR ALTER TRIGGER ORDEN_BI FOR ORDEN
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if ((new.number is null) or (new.number = 0))  then
    new.number = gen_id(GEN_RECORD_NUMBER,1);
  if ((new.guid is null) or (trim(new.guid) = '') ) then
    new.guid = '{' || uuid_to_char( gen_uuid() ) || '}';
end
^
SET TERM ; ^

I also use a GUID column as a unique field