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]
