Choose second lowest occurrence in a trigger

Navigation
1

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

So what I need to do is to insert the data into the table ficha_inscricao check if this candidate was not the one with 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 could not get past syntax errors.

Part of my relational model, which has redundancies

    
asked by anonymous 27.11.2015 / 16:40

1 answer

1

I believe the following query will solve your problem: 

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

Here is a suggestion to edit the trigger 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;
    
27.11.2015 / 17:25
Problem to destroy Delphi DM Minimization Sass css / js