4
votes

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'
1
First, are you sure that you need to denormalize the data this way? Rather than, say, having a materialized view that does an incremental refresh periodically to materialize the count of likes assuming that you actually need to materialize that data for performance reasons. If you do need to materialize the data, are you certain that you really need to use triggers to maintain the data rather than putting that logic in the application layer (i.e. stored procedures that implement the appropriate business logic)? - Justin Cave
This is only the SQL implementation (I'm not using the actual database into an application) and I thought it would be nice to have this trigger that updates the likes, but I'll stick to the COUNT * FROM to get the number of likes. - Cretu Eusebiu
I'm not sure what "I'm not using the actual database into an application" means-- if you're using triggers, you've already left SQL and moved into PL/SQL. If you're using PL/SQL, it's not obvious to me why one PL/SQL solution (triggers) would be acceptable but another (stored procedures) would not be. A materialized view would be even better from that standpoint since it is actually a pure SQL solution. If there is some reason that triggers must be used, that is possible. It's just not in my top, say, 10 approaches in terms of simplicity and maintainability. - Justin Cave
Post the code here. Links can broken over time and then your question will not be helpfull to anyone else. - Jorge Campos
Perhaps I'm misunderstanding something, but IMO you shouldn't be using a trigger to enforce referential integrity. If I understand your design there should be a foreign key constraint on POST_LIKES.POST_ID which references POSTS.ID. By setting this foreign key up so that its delete action is CASCADE the FK constraint should take care of deleting all the POST_LIKES when you delete from POSTS. Best of luck. - Bob Jarvis - Reinstate Monica

1 Answers

0
votes

Look what happens if you delete post. Your trigger tries to delete post_likes. That's why your other trigger tries to decrement posts.likes as you wish. So, you're trying to make unnecessary actions and seeing mutation because deleting on posts leads updating on posts.

All you should do is to break this chain. You should not try to change likes when you deletes post at all. For example, you can use something like

create or replace trigger MY_BEFORE_DELETE_LIKE_TRIGGER ...
begin
  -- Let's do nothing if deleting master too
  if dbms_utility.format_call_stack like '%MY_BEFORE_DELETE_POSTS_TRIGGER%' then
    return;
  end if;
  ...

More correct way is to use flag into packaged variable or something similar.