trigger created but when executed showing some errors==> ORA-04091:, ORA-06512:, ORA-04088:

I have a table called movie_cast.

SQL> select * from  movie_cast;

    ACT_ID     MOV_ID ROLE
---------- ---------- --------------------------------------------------
       101        901 John Scottie Ferguson
       102        902 Miss Giddens
       103        903 T. E. Lawrence
       104        904 Michael
       105        905 Antonio Salieri
       106        906 Rick Deckard
       107        907 Alice Harford
       108        908 McManus
       109        909 J. J. Gittes
       110        910 Eddie Adams
       111        911 Alvy Singer
       112        912 San
       113        913 Adny Dufresne
       114        914 Lester Burnham
       115        915 Rose DeWitt Bukater
       116        916 Sean Maguire
       117        917 Ed
       118        918 Renton
       119        919 Alfred Borden
       120        920 Elizabeth Darko
       121        921 Older Jamal
       122        922 Ripley
       114        923 Bobby Darin

23 rows selected.

This table already has duplicate values(#114). act_id, mov_id are foreign keys. And I’ve created a trigger on movie_cast table called trg1.

SQL> CREATE OR REPLACE TRIGGER trg1
  2  after INSERT ON MOVIE_CAST
  3  FOR EACH ROW
  4  DECLARE
  5  num NUMBER;
  6  BEGIN
  7  SELECT COUNT(act_ID)
  8  INTO num
  9  FROM movie_cast
 10  WHERE mov_ID=:NEW.mov_ID;
 11  if(num >= 1) then
 12  dbms_output.put_line('Already cast assigned,same Actors cant cast more than once');
 13  end if;
 14  END;
 15  /

Trigger created.

Although the trigger is been created but after execution it shows some errors

(SQL> insert into movie_cast values(124, 921, 'abc');
insert into movie_cast values(124, 921, 'abc')
            *
ERROR at line 1:
ORA-04091: table PROJECT_MOVIE_DATA.MOVIE_CAST is mutating, trigger/function may not see it
ORA-06512: at "PROJECT_MOVIE_DATA.TRG1", line 4
ORA-04088: error during execution of trigger 'PROJECT_MOVIE_DATA.TRG1')

My task is: I want to allow entries in this table i.e; an actor can do more than one movie(act_id can be repetitive/duplicate) but I cannot cast same actor for same movie twice. Can somebody help me here? I’m fed up with this question from last 2 days now! Thank You!

Answer

As the commenters mentioned, triggers are not the right tool for preventing duplicates. You want a unique constraint for multiple columns.

ALTER TABLE movie_cast ADD CONSTRAINT uk_movie_cast_actor UNIQUE (mov_id, act_id);

This will allow duplicate MOV_ID and ACT_IDs, but not duplicate combinations of MOV_ID + ACT_ID.