Trigger success with compilation error oracle

Question

I have these tables

PUBRED (cod_pub, title, year, type, medium)
AUTHOR (login, name, surname, institution, country)
AUTHOR_ONE (login, cod_pub)
PUB_CITA (cod_pub, cod_pub_cited)

And I have to get that citations to a publication should always be after it

I have the following trigger but it gives me compilation errors and I don't find any errors looking in other questions

Create or replace trigger works_trg_02
Before insert or update on pub_cita
For each row
Declare
Cnt number;
Begin
Select count (*) into cnt
      from pubred pr
      inner join pub_cita pc
         on :new.pc.cod_pub = pr.cod_pub
      inner join pubred pr2
         on :new.pc.cod_pub_cited = pr2.cod_pub
      where pr.year < pr2.year then 1
Else 0 end = 0;
If cnt <> 0 then
Raise_application_error ('-20001', 'Not possible');
End if;
End works_trg_02;
/

I get the typical error from 'ORA-24344: success with compilation error'

And when doing the inserts to prove it I get 'ORA-04098: trigger 'FIDDLE_WIXYFBGEUZDXILZQBEGR.WORKS_TRG_02' is invalid and failed re-validation'

I have searched other similar questions that have been asked around here adapting my initial solution and I have not been successful and I believe that everything there is is correct.

Answer 1

First, when you get ORA-24344: success with compilation error, you should run show errors, or query the user_errors or all_errors views to find your actual error message.

Second, this is wrong: :new.pc.cod_pub. The :new variable is a record, the row of the table (pub_cita) currently being inserted. You can just say :new.cod_pub.

Third, a trigger can't query its own table - so you can't have pub_cita in your SELECT query. Which is fine, you can refer to it using :new.

Fourth, what is this?

where pr.year < pr2.year then 1
Else 0 end = 0;

I think you started a CASE statement, then deleted part of it? It's not valid syntax.

See if this works for you:

Create or replace trigger works_trg_02
Before insert or update on pub_cita
For each row
Declare
    Cnt number;
Begin
    Select count (*) into cnt
          from pubred pr
          inner join pubred pr2
             on :new.cod_pub_cited = pr2.cod_pub
          where pr.year < pr2.year
            and pr.cod_pub = :new.cod_pub
          ;
    If cnt <> 0 then
        Raise_application_error ('-20001', 'Not possible');
    End if;
End works_trg_02;
/