I have a
posts(id, likes) table (likes = number of likes for that post)
and a
post_likes(post_id, user_id) table (the users who liked a post).
I have a trigger (post_likes_after_insert)
so when I add a new like it increments posts.likes and another trigger (post_likes_before_delete) that decrements posts.likes. Everything works fine.
Then I have another trigger (before delete) for the posts, to delete the post_likes, but this will trigger the trigger above that decrements the posts.likes column.
So it gives me this error:
table posts is mutating, trigger/function may not see it at post_likes_before_delete, line 4; error durring execution of trigger post_likes_before_delete
Here is a pastebin with the code and the full error at the bottom. http://pastebin.com/EQHTLZFp
Thanks.
-----------------------------------------------------------------------
-- USERS
-----------------------------------------------------------------------
CREATE TABLE users
(
id NUMBER(10) PRIMARY KEY NOT NULL,
email CHAR(100) UNIQUE NOT NULL,
password CHAR(60) NOT NULL,
first_name CHAR(30) NOT NULL,
last_name CHAR(30) NOT NULL,
birth_date DATE NULL,
gender CHAR(10) NULL,
joined TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
CONSTRAINT gender_check CHECK (UPPER(gender) IN ('M', 'F'))
);
-- Auto increment for users.id
DROP SEQUENCE users_sequence;
CREATE SEQUENCE users_sequence START WITH 1 INCREMENT BY 1;
CREATE TRIGGER users_before_insert
BEFORE INSERT
ON users REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT users_sequence.nextval INTO :NEW.ID FROM dual;
END;
/
--
-- POSTS
--
CREATE TABLE posts
(
id NUMBER(10) PRIMARY KEY NOT NULL,
user_id NUMBER(10) REFERENCES users(id) NOT NULL,
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
content char(1000) NOT NULL,
likes NUMBER(10) DEFAULT 0 NOT NULL,
privacy CHAR(10) DEFAULT 'public' NOT NULL,
CONSTRAINT fk_posts FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
-- Auto increment for posts.id
DROP SEQUENCE posts_sequence;
CREATE SEQUENCE posts_sequence START WITH 1 INCREMENT BY 1;
CREATE TRIGGER posts_before_insert
BEFORE INSERT
ON posts REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT posts_sequence.nextval INTO :NEW.ID FROM dual;
END;
/
CREATE TRIGGER posts_before_delete
BEFORE DELETE
ON posts
FOR EACH ROW
BEGIN
DELETE FROM post_likes WHERE post_id = :old.id;
END;
/
--
-- POST_LIKES
--
CREATE TABLE post_likes
(
user_id NUMBER(10) NOT NULL REFERENCES users(id),
post_id NUMBER(10) NOT NULL REFERENCES posts(id),
PRIMARY KEY (user_id, post_id),
--CONSTRAINT fk_post_likes1 FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
CONSTRAINT fk_post_likes2 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
-- Increments posts.likes
CREATE TRIGGER post_likes_after_insert
AFTER INSERT
ON post_likes
FOR EACH ROW
DECLARE
n NUMBER(10);
BEGIN
SELECT likes INTO n FROM posts WHERE id = :new.post_id;
UPDATE posts SET likes = n+1 WHERE id = :new.post_id;
END;
/
-- Decrements posts.likes
CREATE TRIGGER post_likes_before_delete
BEFORE DELETE
ON post_likes
FOR EACH ROW
DECLARE
n NUMBER(10);
BEGIN
SELECT likes INTO n FROM posts WHERE id = :old.post_id;
UPDATE posts SET likes = n-1 WHERE id = :old.post_id;
END;
/
-- Full error, EUSEBIU is the username that I'm logged in
-- ERROR at line 1:
-- ORA-04091: table EUSEBIU.POSTS is mutating, trigger/function may not see it
-- ORA-06512: at "EUSEBIU.POST_LIKES_BEFORE_DELETE", line 4
-- ORA-04088: error during execution of trigger 'EUSEBIU.POST_LIKES_BEFORE_DELETE'
-- ORA-06512: at "EUSEBIU.POSTS_BEFORE_DELETE", line 2
-- ORA-04088: error during execution of trigger 'EUSEBIU.POSTS_BEFORE_DELETE'