"gen_id()" or "select max()" for Auto Increment in a PK field?

Asked

Viewed 573 times

0

Currently I use a Trigger with this SQL code to get an Auto Increment to be assigned in the primary key field (PK) of the tables:

create or alter trigger TRI_CLIENTES_BI for CLIENTES
active before insert position 0
as
begin
  if (new.ID is null) then -- linha 1
    select coalesce(max(ID),0)+1 from CLIENTES into new.ID; -- linha 2
end

What "line 2" does is take the last "ID" incrementing 1 and assign it to "new.ID" if "new.ID" is null before entering the record. I see many examples using another method in "line 2" which is the following:

...
    new.ID = gen_id(GEN_CLIENTES,1);
...

In this case, the "new.ID" is receiving the value of the Generator incrementing 1.

I believe that the first method leaves the code leaner and better maintenance, already avoiding creating a Generator for each table and have to reset each one when necessary.

Can the first method cause some kind of conflict when there are many simultaneous transactions? The second method would be more appropriate and better performance because it does not use select?

firebird trigger

1 answer

2


It is always recommended to use generators or sequences to obtain auto incrementable values, as it is the responsibility of the DBMS to manage transaction competition, which ensures much more reliability and consistency in the data.

And this type of auto increment is always less expensive for SGDB than a select in a table.

Browser other questions tagged firebird trigger

You are not signed in. Login or sign up in order to post.