Reputation: 1259
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
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