Table Is Mutating, Trigger/Function May Not See It (Stopping an Average Grade from Dropping Below 2.5)

Table is mutating, trigger/function may not see it (stopping an average grade from dropping below 2.5)

I think you can fix this by rewriting this as a before trigger, rather than an after trigger. However, this might be a little complicated for inserts and deletes. The idea is:

CREATE OR REPLACE TRIGGER stopChange
    BEFORE UPDATE OR INSERT OR DELETE ON taking
    REFERENCING OLD AS old
    NEW AS new
    FOR EACH ROW
DECLARE

grd_avg taking.grade%TYPE;

BEGIN
    SELECT (SUM(grade) - oldgrade + new.grade) / count(*)
    INTO grd_avg
    FROM taking
    WHERE studentnum = :new.studentnum
    AND schedulenum = :new.schedulenum
    AND semester = :new.semester;

    IF grd_avg < 2.5 THEN
        new.grade = old.grade
    END IF;
END;  

Trigger Code returns 'Mutating' error

Rewrite the trigger (or function) so it does not read that table. The trigger needs to be rewritten as

create or replace TRIGGER test_trigger
AFTER INSERT
ON xml_hours_load
FOR EACH ROW
WHEN (new.ROW_ID IS NOT NULL)
    DECLARE

    BEGIN

    INSERT INTO xml_hours_load_2
    VALUES(:new.row_id, '2', '3', '5', '6', 'hi');
    END;

The :new value is the value which is modified in table xml_hours_load

Oracle Trigger: Show an error in a table in database A if the value does not exist in database B

Your update trigger is executed for each row of the table that meets the conditions of your update statement.

In the second case

UPDATE student
SET Grade = 'C'
WHERE Student_Name = 'James'
AND University = 'something_that_doesnt_exist';

no rows are affected, therefore the trigger is not executed at all.

How to create a trigger which, update a row in the same table after insert?

You need a before insert trigger like this:

create or replace trigger id_to_id_2
before insert
on t_1
for each row
begin
  :new.id_2 := :new.id;
end;

Is there a way to create multiple DELETE triggers on the same table?

The documentation says:

SQLite supports only FOR EACH ROW triggers, not FOR EACH STATEMENT triggers.

And the information you have in the WHEN clause is only the contents of the record to be deleted.

You cannot differentiate these cases from within a trigger.
You have to implement this in your application.

Subquery function inside trigger when clause

take it as suggestion

  CREATE OR REPLACE TRIGGER DeleteStudentResults
  BEFORE DELETE ON SESSION_RESULTS
  REFERENCING OLD AS old NEW AS new
  FOR EACH ROW
  declare 
  avg_result number;
  BEGIN
  (SELECT AVG(RESULT) into avg_result from session_results where STUDENT_ID = :old.STUDENT_ID);
  if(avg_result>=3) then
   raise_application_error (-20100, 'You can not delete initial record');
   end if;
 END;

---

Related Topics