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

0

I currently use a Trigger with this SQL code to get an Auto Increment to be assigned in the primary key field ( PK ) of 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 get the last "ID" by incrementing 1 and assign it to "new.ID" if "new.ID" is null before inserting the record. I see many examples using another method in "line 2" which is as follows:

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

In this case, the "new.ID" is getting the Generator value by incrementing 1.

I think the first method leaves the code leaner and better maintained, avoiding creating a Generator for each table and having to reset each one as needed.

Can the first method cause some sort of conflict when there are too many concurrent transactions? The second method would be more appropriate and of better performance for not using select ?

    
asked by anonymous 07.11.2017 / 16:53

1 answer

2

It is always recommended to use generators or sequences to get auto-incremental values, as it is up to the DBMS to manage transaction bids, which ensures much more reliability and consistency of data.

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

    
07.11.2017 / 17:55