#1442 - Can't update table 'article_comment'

Databases
1

I just created the following MySQL Trigger…


DELIMITER //
CREATE TRIGGER article_comment_update_commentno_ai AFTER INSERT ON article_comment
FOR EACH ROW
BEGIN
  UPDATE article_comment
  SET comment_no = (SELECT MAX(comment_no) FROM article_comment WHERE article_id = NEW.article_id) + 1
  WHERE id = LAST_INSERT_ID()
  LIMIT 1;

END;
//
DELIMITER ;

The goal of this Trigger is to prevent an earlier problem that I had where several users are submitting a Comment at the same time, and so the maximum “comment_no” is getting screwed up due to concurrent activity.

Unfortunately, when I run this test INSERT…


INSERT INTO article_comment(article_id, member_id, body, created_on)
  VALUES(5, 25, 'Comment #18', NOW())

…I am getting the following error message…


[COLOR="#FF0000"]#1442 - Can't update table 'article_comment' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
[/COLOR]

What am I doing wrong?

By the way, here is my article_comment table…


CREATE TABLE `article_comment` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '(PK)',
 `article_id` mediumint(8) unsigned NOT NULL COMMENT '(UK1)(FK)',
 `member_id` mediumint(8) unsigned NOT NULL COMMENT '(UK2)(FK)',
 `comment_no` smallint(5) unsigned NOT NULL,
 `body` text NOT NULL,
 `comment_approved` tinyint(1) NOT NULL DEFAULT '1',
 `created_on` datetime NOT NULL COMMENT '(UK3)',
 `approved_on` datetime DEFAULT NULL,
 `updated_on` datetime DEFAULT NULL,
 `admin_updated_on` datetime DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `idx_u_article_member_created` (`article_id`,`member_id`,`created_on`),
 KEY `idx_article_id` (`article_id`),
 KEY `idx_member_id` (`member_id`),
 CONSTRAINT `article_comment_ibfk_1` FOREIGN KEY (`article_id`) REFERENCES `article` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `article_comment_ibfk_2` FOREIGN KEY (`member_id`) REFERENCES `member` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=223 DEFAULT CHARSET=latin1 COMMENT='Member''s Comments to Article'

Sincerely,

Debbie

P.S. See @r937 ; , I am trying my best to problem solve my Calculate MAX()+1 and INSERT all-in-one by researching and testing on my own!! :wink: