Generate Sequence Records with ClientDataSet (MultiUser)

Well, after some tests, I came to a point that I could solve as follows:

I use a trigger before insert (Firebird) to generate the sequential. The code looks like this:

  SELECT COALESCE(MAX(NUMERO_PEDIDO) + 1,1) FROM PEDIDO
   WHERE EMPRESA = NEW.EMPRESA 
    INTO NEW.NUMERO_PEDIDO;

  IF (EXISTS(SELECT NUMERO_PEDIDO FROM PEDIDO       
              WHERE EMPRESA = NEW.EMPRESA
                AND NUMERO_PEDIDO = NEW.NUMERO_PEDIDO)) THEN
  BEGIN
    SELECT COALESCE(MAX(NUMERO_PEDIDO) + 1,1) FROM PEDIDO
     WHERE EMPRESA = NEW.EMPRESA 
      INTO NEW.NUMERO_PEDIDO;
  END

It is interesting to leave only the first block to generate the sequential without the test and two or more people include a record of the primary key error.

Your mistake is to use select Max () to do the key increment. Because transactions see different versions of the data until they are committed, each transaction will return the same value in Max (). So you have a primary key error. And the same thing happens in triggers, since they see only the version of the data within your transaction. That is, they will give primary key error sooner or later.

For self-improvement, there are two strategies: The first and easiest is to use sequences / generator. In the trigger you can write a code similar to the following:

if (new.id = 0) then
  new.id = gen_id(meu_generator,1);

The only problem is that you may not have a sequential number there, since with each call to gen_id, you increment the generator and lose a number (in case the transaction results in a rollback).

The second (and more complicated) strategy is to use a control table. The usage would look something like this:

Você trava a tabela;
Resgata um número;
Libera a tabela.

And when canceling, you would reverse:

Você trava a tabela;
Devolve um número;
Libera a tabela.

The problem is that you run the serious risk of deadlocking and the application is slow to respond.

As this is an order number, I think the most interesting thing for you would be to use the first strategy, incrementing the sequence in the save process. And I would say more: do this in the application. When you change banks, it will be a damn job to take all the triggers out of the bank or toll them.