raise_application error not working in oracle 11g-CodePudding

I have been trying PL/SQL for quite some time now and I've tried creating a procedure and a trigger but I'm unable to get the required output. Let me share what I've done till now.

==> create or replace procedure cast(act_id movie_cast.act_id%TYPE, mov_id movie_cast.mov_id%TYPE, role movie_cast.role%TYPE)
        IS
        begin
        insert into movie_cast values(act_id, mov_id, role);
        end;
        /
        
        Procedure created.
        
==>> SQL> create or replace trigger trg1
          2  before insert on movie_cast
          3  for each row
          4  declare
          5  num number;
          6  begin
          7  select count(act_id) into num from movie_cast where mov_id = :new.mov_id;
          8  if(:old.mov_id = :new.mov_id AND :old.act_id = :new.act_id) then
          9  raise_application_error(-20001, 'Actor already in the movie!');
         10  end if;
         11  end;
         12  /
        
        Trigger created.

I've customize the error in line number 9 and I'm not getting the error there. Like when I'm fullfilling this condition==> if(:old.mov_id = :new.mov_id AND :old.act_id = :new.act_id), I want an error but I'm not getting it. Can anybody help me with it? Thank you so much!

CodePudding user response：

That's a wrong approach.

A row-level trigger on movie_cast that selects from the same table will raise a mutating table error.

Correct way to do that is to create unique index (or unique / primary key constraint) on those columns, e.g.

create unique index on movie_cast (mov_id, act_id);

CodePudding user response：

Answering your question- :old values are null in before trigger, thats why your condition will be never fullfilled, never ever- even if you insert row with nulls, null=null condition wont pass, null is null is TRUE, but null = null will give you FALSE, atleast for Oracle DB.