Trigger not executing as expected-CodePudding

I'm trying to implement this trigger that ensures an user can't comment on their own publications.

CREATE OR REPLACE FUNCTION not_comment() RETURNS TRIGGER AS 
$BODY$ 

BEGIN 
       IF EXISTS (SELECT *
        FROM 
        (SELECT publisherID FROM comment INNER JOIN post USING (postID) WHERE NEW.postID = post.postID) AS comment_userID,
        (SELECT publisherID FROM article INNER JOIN post USING (postID) WHERE NEW.articleID = article.articleID) AS article_userID
        WHERE comment_userID.publisherID = article_userID.publisherID) THEN
       RAISE EXCEPTION 'A user cannot comment an article that publish'; 
       END IF; 
       RETURN NEW; 
END 
$BODY$ 
LANGUAGE plpgsql; 
 
CREATE TRIGGER not_comment 
      BEFORE INSERT ON comment
      FOR EACH ROW 
      EXECUTE PROCEDURE not_comment();

The inner query is working as expected for the test cases, but for some reason the condition isn't being activated.

The same syntax as other working triggers was used.

Any idea of what's going on?

CodePudding user response：

The trigger is fired before the row is inserted, so you should assume in the condition that the row is already in the table. Thus the first subquery should look like

    (SELECT publisherid FROM post WHERE new.postid = post.postid) AS comment_userID