The MAX is forcing through all the records.

select coalesce(max(o.n_orden),0)+1
from ordenes o
where o.year_orden=new.year_orden
into new.n_orden


You could create an intermediate table to keep N_ORDEN:

CREATE TABLE YEAR_ORDEN (
YEAR_ORDEN INTEGER NOT NULL,
N_ORDEN SMALLINT NOT NULL,

PRIMARY KEY (YEAR_ORDEN, N_ORDEN)
);


CREATE OR ALTER TRIGGER MANTENER_NORDEN FOR ORDENES
ACTIVE BEFORE INSERT POSITION 0
AS
begin
/* N_ORDEN, de tabla intermedia y uso optimo del indice */
select Y.n_orden from YEAR_ORDEN Y
where Y.year_orden = new.year_orden
into new.n_orden;

if (new.n_orden is null)
then new.n_orden = 1;

update or insert into year_orden (year_orden, n_orden)
values (new.year_orden, new.n_orden + 1)
matching (year_orden);

end


Regards.

Christian


[Non-text portions of this message have been removed]