Sunday, 20 May 2018

ORA-04091: table XXX is mutating, trigger/function may not see it

Requirement: Solution for mutating table error. The error description is given below.

Error: Error description is given below.

ORA-04091: table SYSTEM.STUDENTS is mutating, trigger/function may not see it
ORA-06512: at "SYSTEM.TRG_STUDENT", line 6
ORA-04088: error during execution of trigger 'SYSTEM.TRG_STUDENT'

Environment Set Up: We will be using the Oracle 11g XE for our demonstration purpose.
Let us create the PROFESSORS table using the below create statement.

CREATE TABLE professors (
    professor_id      NUMBER(10) PRIMARY KEY,
    prof_first_name   VARCHAR2(15),
    prof_last_name    VARCHAR2(15)
);

Insert data into the PROFESSORS table using the below insert statements.

INSERT INTO professors VALUES (1,'Debabrata','Mazumdar');
INSERT INTO professors VALUES (2,'Moumita','Mitra');
INSERT INTO professors VALUES (3,'Diphlu','Mondal');

Let us create the STUDENTS table using the below create statement.

CREATE TABLE students (
    student_id       NUMBER(10) PRIMARY KEY,
    stu_first_name   VARCHAR2(15),
    stu_last_name    VARCHAR2(15),
    professor_id     NUMBER(10),
    CONSTRAINT fk_professor_id FOREIGN KEY ( professor_id )
        REFERENCES professors ( professor_id )
);

Insert data into the STUDENTS table using the below insert statements.

INSERT INTO students VALUES (1,'Susanto','Paul',1);
INSERT INTO students VALUES (2,'Shuhansh','Paul',1);
INSERT INTO students VALUES (3,'Anjali','Paul',1);
INSERT INTO students VALUES (4,'Moumita','Deb',2);
INSERT INTO students VALUES (5,'Ankur','Paul',1);
INSERT INTO students VALUES (6,'Mousumi','Modak',1);
INSERT INTO students VALUES (7,'Sunita','Deb',2);
INSERT INTO students VALUES (8,'Susanto','Paul',3);

COMMIT the above changes.

Thus the data in the PROFESSORS table is shown below.

SELECT * FROM professors;

Thus the data in the STUDENTS table is shown below.

SELECT * FROM students;

Replication of mutating table error: Let us create a TRIGGER named trg_student. The purpose of this trigger is to check the number of students each processor is teaching. Now we will try to update/insert a new record in the STUDENTS table. But before insert or update of STUDENTS table, it should check the number of students each professor is teaching. If the number of students taught by each professor is more than 5, immediate it should through exception saying “Professor 1 is already occupied.” Else it should update the table.

CREATE OR REPLACE TRIGGER trg_student BEFORE
    INSERT OR UPDATE ON students
    FOR EACH ROW
DECLARE
    v_total   NUMBER(10);
    v_name    VARCHAR2(30);
BEGIN
    --Check if the current professor is overbooked.
    SELECT COUNT(*)INTO v_total FROM students
        WHERE professor_id =:new.professor_id;
    IF v_total >= 5 THEN
        SELECT prof_first_name || ' ' || prof_last_name
            INTO v_name FROM professors
WHERE professor_id =:new.professor_id;
        raise_application_error(-20000,'Professor '
                    || v_name || ' is already occupied.');
    END IF;
EXCEPTION
    WHEN no_data_found THEN
        raise_application_error(-20001,'This is not a valid professor');
END;

Now let us issue the below update statement against the STUDENTS table.

UPDATE students SET professor_id = 1 WHERE student_id = 7;

When we issued the above update statement against the STUDENTS table, the following error is encountered.

Error starting at line : 1 in command -
UPDATE students SET professor_id = 1 WHERE student_id = 7
Error report -
ORA-04091: table SYSTEM.STUDENTS is mutating, trigger/function may not see it
ORA-06512: at "SYSTEM.TRG_STUDENT", line 6
ORA-04088: error during execution of trigger 'SYSTEM.TRG_STUDENT'

Thus the mutating table error is replicated.

The solution for mutating table error: For the solution of the above mutating table error follow the steps as shown below:

Step 1: To record the professor_id and name, two global variables must be declared with the help of the PL/SQL package a shown below.

CREATE OR REPLACE PACKAGE professor_gv AS
    g_professor_id professors.professor_id%TYPE;
    g_name VARCHAR2(30);
END;

This package specification contains declarations for the two global variables, g_professor_id and g_name.

Step 2: An existing trigger trg_student must be modified so that its record's the professor_id, queries the professors' table, and records the name.

CREATE OR REPLACE TRIGGER trg_student BEFORE
    INSERT OR UPDATE ON students
    FOR EACH ROW
BEGIN
    IF :new.professor_id IS NOT NULL THEN
    BEGIN
    -- Assign new professor_id to the global variable g_professor_id
        professor_gv.g_professor_id := :new.professor_id;
        SELECT prof_first_name || ' ' || prof_last_name
            INTO professor_gv.g_name FROM professors
            WHERE professor_id = professor_gv.g_professor_id;
        EXCEPTION
            WHEN no_data_found THEN
                raise_application_error(-20001,'This is not a valid professor');
        END;
    END IF;
END;

In the above trigger stu_trigger, the global variables g_professor_id and g_name are initialized if the incoming value of the professor’s id is not null. The variables names are prefixed by the package name- a convention dot notation.

Step 3: A new trigger must be created on the students table. This trigger should be statement level trigger which will be fired after the INSERT or UPDATE statement has been issued.

CREATE OR REPLACE TRIGGER trg_students_2 AFTER
    INSERT OR UPDATE ON students
DECLARE
    v_total   NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_total FROM students
        WHERE professor_id = professor_gv.g_professor_id;
    IF v_total >= 5 THEN
        raise_application_error(-20000,'Professor '
        || professor_gv.g_professor_id|| ' is already occupied.');
    END IF;
END;

Step 4: Update students table using the below query.

UPDATE students SET professor_id = 1 WHERE student_id = 7;

Output: Thus we can see in the below output that we are no more getting the mutating table error. It is throwing “ORA-20000: Professor 1 is already occupied.” Which we are throwing explicitly.

Error starting at line : 1 in command -
UPDATE students SET professor_id = 1 WHERE student_id = 7
Error report -
ORA-20000: Professor 1 is already occupied.
ORA-06512: at "SYSTEM.TRG_STUDENTS_2", line 7
ORA-04088: error during execution of trigger 'SYSTEM.TRG_STUDENTS_2'

Hence, the solution for our requirement.

If you like the post, please comment, share the post and do like me on Facebook

Thanks & Regards,
Susanto Paul.