Choose second smallest occurrence on a Rigger

Question

Trigger that prevents a candidate who has stayed in the penultimate place during the last two months to apply for a new job

so what I need to do is before entering the data into the table ficha_inscricao verify that this candidate was not the one who had the second worst classificacao_final.nota_final

CREATE OR REPLACE TRIGGER impede_candidato
BEFORE INSERT ON ficha_inscricao
WHEN (:new.candidatoBI = candidato.BI and
      candidato.bi=classificacao_final.candidatoBI and 
      classificacao_final.nota_final ............)

DECLARE impedido EXCEPTION;

BEGIN 
  RAISE impedido;
  EXCEPTION 
    WHEN impedido THEN RAISE_APPLICATION_ERROR (-20001, 'Não se pode inscrever');
END;

I tried to do with order by DESC plus rownum=2 but I couldn’t get past the syntax errors.

Part of my relational model, which has redundancies

Answer 1

I believe the following query solve your problem:

SELECT *
FROM Candidato
WHERE BI = (
    SELECT CandidatoBI
    FROM ClassificacaoFinal
    ORDER BY NotaFinal ASC
    LIMIT 1, 1
)

Follow a suggested edit on Rigger with query above:

CREATE OR REPLACE TRIGGER impede_candidato
BEFORE INSERT ON ficha_inscricao
WHEN (:new.candidatoBI = (
    SELECT CandidatoBI
    FROM ClassificacaoFinal
    ORDER BY NotaFinal ASC
    LIMIT 1, 1)
)

DECLARE impedido EXCEPTION;

BEGIN 
  RAISE impedido;
  EXCEPTION 
    WHEN impedido THEN RAISE_APPLICATION_ERROR (-20001, 'Não se pode inscrever');
END;