CODEWITHSUNDEEP
sqloracle-databaseplsqltriggers
David Edgar
David Edgar

Reputation: 505

Update view not legal even after creating INSTEAD OF UPDATE TRIGGER

My table of ADMITED_TABLE contains (NOSTUDENT, COURSECODE, SEMESTER, NOGROUPE,MARK) Columns.

What I want to do:

When the update is excecuted I want to increase concerned students mark by the diffrence between the oldavg and newavg. 

Eg: newavg - oldavg = 10.
    every student of that cours get  MARK+10

Remarque : is it possible to update a none Column value in this case AVGMARK which is just a name given to AVG(MARK) ?

UPDATE AverageByGroup SET AVGMARK = (AVGMARK + 10) -- error is her
WHERE COURSCODE = 'AAAA' 
AND   NOGROUPE = 111
AND   SEMESTER = 0000;

Result :

I should see in ADMITED_TABLE concerned students with old mark + 10

Error: 

SQL : ORA-01732: data manipulation operation not legal on this view
01732. 00000 -  "data manipulation operation not legal on this view"

My view works and my TRIGGER compile without error

-- CREATION OF THE VIEW

CREATE OR REPLACE VIEW AverageByGroup  AS
SELECT COURSCODE, NOGROUPE, SEMESTER, AVG(MARK) AS AVGMARK
FROM ADMITED_TABLE GROUP BY COURSECODE,NOGROUPE,SEMESTER;

-- TRIGGER INSTEAD OF UPDATE
CREATE OR REPLACE TRIGGER changeStudentsMarks
INSTEAD OF UPDATE ON AverageByGroupe  
FOR EACH ROW

DECLARE

    -- UPDATE CURSOR
    CURSOR c_students IS
    SELECT * FROM ADMITED_TABLE WHERE 
                    COURSCODE  = :NEW.COURSCODE
                    AND NOGROUPE = :NEW.NOGROUPE
                    AND SEMESTER    = :NEW.SEMESTER 
                    FOR UPDATE OF MARK;

    rec_students c_students%ROWTYPE;

    v_actual_avg  INTEGER;
    V_new_avg     INTEGER; 
    v_diff        INTEGER;

BEGIN
    -- Affectationa
    v_actual_avg    :=  :OLD.AVGMARK;
    v_new_avg       :=  :NEW.AVGMARK; 
    v_diff          :=  v_new_avg - v_actual_avg;

    OPEN c_students;
    LOOP
        FETCH c_students INTO rec_students;
        EXIT WHEN c_students%NOTFOUND;

        v_maj_note_etudiant :=  rec_etudiants.NOTE + v_diff -- add the diffrence to student mark

        UPDATE ADMITED_TABLE
        SET MARK  =  MARK + v_maj_note_etudiant
        WHERE CURRENT OF c_students;
    END LOOP;
    CLOSE c_students;
END;
/

Upvotes: 1

Views: 104

Answers (1)

DCookie
DCookie

Reputation: 43533

From the documentation:

If a view contains pseudocolumns or expressions, then you can only update the view with an UPDATE statement that does not refer to any of the pseudocolumns or expressions.

So, no you cannot update the column derived from the AVG function.

Upvotes: 1

Related Questions