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

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!

CodePudding user response：

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.

CodePudding user response：

You get the ORA-040 mutating error because your trigger refers to the table it fires on. In Oracle you basically cannot do this. The proper way to handle this is to create the unique constant then intercept and process the the exception when it occurs, either in the plsql procedure or in the apps error handling. See demo.

create or replace 
procedure generate_actor_movie_role( 
          actor_id_in movie_cast.act_id%type
        , movie_id_in movie_cast.mov_id%type
        , role_in     movie_cast.role%type
        ) 
is 
begin 
    insert into movie_cast (act_id, mov_id, role)  
         values ( actor_id_in, movie_id_in, role_in); 
exception 
    when dup_val_on_index then
         begin 
             log_error( 'generate_actor_movie_role'
                       , dbms_utility.format_error_stack
                       ); 
             raise_application_error ( -20001, 'Already cast assigned,same Actors cant cast more than once');
         end ; 
end generate_actor_movie_role;

Note: Using dbms_output is virtually useless, at least in a production environment, as it is never seen. It also actually converts the exception customized error, but still as an exception. The demo additionally contains minimalist error logging procedure.
Take note of the commit from @stickybit as actors play more than one role in a given movie. Perhaps your constraint should include role.