title: oracle triggers error ORA-01427 & error ORA-04091
body: <p>I have the following tables:</p>
  
  <p><strong>FACULTY table</strong></p>
  
  <pre><code>CREATE TABLE  "FACULTY" 
     (    "FACULTY_ID" NUMBER(3,0), 
      "FACULTY_NAME" VARCHAR2(30), 
      "FACULTY_DEAN" VARCHAR2(30), 
       CONSTRAINT "FACULTY_PK" PRIMARY KEY ("FACULTY_ID") ENABLE
     )
  </code></pre>
  
  <p><strong>COURSE table</strong></p>
  
  <pre><code>CREATE TABLE  "COURSE" 
     (    "COURSE_ID" NUMBER(5,0), 
      "COURSE_NAME" VARCHAR2(50), 
      "COURSE_LEVEL" NUMBER, 
      "FACULTY" NUMBER, 
       CONSTRAINT "COURSE_PK" PRIMARY KEY ("COURSE_ID") ENABLE
     )
  </code></pre>
  
  <p>so now i want to achieve two things</p>
  
  <p>(1) when a faculty_id is updated on the faculty table. a trigger will fire and update the corresponding rows in the course table with new faculty_id. it will also store the old faculty_id value, name of course, and the date in which the operation is performed in a course_log table.</p>
  
  <p>Below is what I got</p>
  
  <pre><code>create or replace trigger update_faculty
  after update on faculty
  for each row
  begin
     insert into course_log 
       values (:old.faculty_id, 
               (select course_name 
                  from course 
                 where faculty=:old.faculty_id),
               sysdate);
     update course 
        set faculty=:new.faculty_id 
      where faculty=:old.faculty_id;
  end;
  </code></pre>
  
  <p>But I get the following error. </p>
  
  <blockquote>
    <p>error ORA-01427: single-row subquery returns more than one row ORA-06512: at "SYSTEM.UPDATE_FACULTY", line 2 ORA-04088: error during execution of trigger 'SYSTEM.UPDATE_FACULTY'</p>
  </blockquote>
  
  <p>any ideas on how to solve it?</p>
  
  <p>(2) Write a trigger that fires when try change the course_id attribute in the course table, which will check whether the value already exists in the course table and will update successfully if it is a new value. If the value already exists in any row, the trigger will throw an application error saying "The course_id already exists! Update not successful."</p>
  
  <p>below is my query</p>
  
  <pre><code>CREATE OR REPLACE TRIGGER  "UPDATE_COURSE_ID" 
  after update on course
  for each row
  declare 
      error number;
  begin
      select count(*) 
        into error 
      from course
      where course_id=:new.course_id;
  
      if error &gt; 0 then
          raise_application_error (-20000,'The course_id already found! Update not success'); 
      end if;
      if error = 0 then
          update course set course_id=:new.course_id where course_id=:old.course_id;
      end if;
  end;
  </code></pre>
  
  <p>But I got this error</p>
  
  <blockquote>
    <p>error ORA-04091: table SYSTEM.COURSE is mutating, trigger/function may not see it ORA-06512: at "SYSTEM.UPDATE_COURSE_ID", line 5 ORA-04088: error during execution of trigger 'SYSTEM.UPDATE_COURSE_ID'</p>
  </blockquote>