CODEWITHSUNDEEP
oracle-databaseplsql
metraon
metraon

Reputation: 1259

Trigger update in Oracle, mutating error

I have this simple table called Favorites.

Favorites
| username | type_of_movie | like_or_dislike |

The data looks like this :

AAA, Action, Like
AAA, Romance, Dislike
...

I have made a trigger to count the maximum favorite types and prevent the user to like all the genre.

CREATE OR REPLACE TRIGGER trgLike
BEFORE INSERT OR UPDATE ON Favorite
FOR EACH ROW
DECLARE
    count number;
BEGIN
 SELECT
 COUNT(username) INTO count
 FROM
 Favorite
 WHERE
 username= :NEW.username AND like_or_dislike = 'Like';
 IF (count = 3) THEN
    RAISE_APPLICATION_ERROR(-20000,'Too much liking');
 END IF;    
END;
/

I want the users just to be able to like 3 genre of movies.

The insert trigger works pretty well but when I try to update something to dislike, I get and error ORA-04091 table is in mutation. Error at line 6.

How can I prevent this? I have searched and it seems that my update will change the value of my select but I don't see how.

I am using Oracle version 11g.

Upvotes: 2

Views: 492

Answers (2)

Dart XKey
Dart XKey

Reputation: 54

You can use materialized view in your trigger.

Upvotes: 0

Trinimon
Trinimon

Reputation: 13967

The error message appears, because your trigger queries the Favorite table at the same time while table contents are changing (either UPDATE or INSERT).

In order to work around this issue you'll need three triggers and a small package:

CREATE OR REPLACE PACKAGE state_pkg AS
    type ridArray IS TABLE OF rowid INDEX BY binary_integer;
    newRows ridArray;
    empty ridArray;
END;
/

CREATE OR REPLACE TRIGGER trgLike_clear_table 
    BEFORE INSERT OR UPDATE ON Favorite 
BEGIN 
    state_pkg.newRows := state_pkg.empty;
END;
/

CREATE OR REPLACE TRIGGER trgLike_capture_affected_rows 
    AFTER INSERT OR UPDATE ON Favorite FOR EACH ROW 
BEGIN 
    state_pkg.newRows(state_pkg.newRows.count +1) := :new.rowid;
END;
/

CREATE OR REPLACE TRIGGER trgLike_do_work 
    AFTER INSERT OR UPDATE ON Favorite 
DECLARE    
    likes NUMBER;
BEGIN 
  FOR i IN 1..state_pkg.newRows.count LOOP
    SELECT COUNT(*) INTO likes
      FROM Favorite
    WHERE username = (SELECT username FROM Favorite WHERE rowid = state_pkg.newRows(i))
      AND like_or_dislike = 'Like';
      IF (likes = 3) THEN
          RAISE_APPLICATION_ERROR(-20000, 'Too much liking');
      END IF;
    END LOOP;
END;
/

There is a nice article about this at AskTom.

p.s.: see updated and tested version above.

Upvotes: 3

Related Questions