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
?