mysql - 当该类别的任务插入到任务表中时，我想增加该类别的任务计数

Question

CREATE TRIGGER tr_task_forinsert
AFTER INSERT ON task for each row
BEGIN
    SET @count = (select countoftask from category where catid = NEW.catid and userid=NEW.userid);

    UPDATE category 
    SET countoftask = @count+1 
    WHERE catid=NEW.catid and userid=NEW.userid;
END

哪里出错了？

任务表

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| taskid   | int(11)      | NO   | PRI | NULL    | auto_increment |
| taskname | varchar(255) | YES  |     | NULL    |                |
| catid    | int(11)      | YES  | MUL | NULL    |                |
| userid   | int(11)      | YES  | MUL | NULL    |                |
| taskdate | date         | YES  |     | NULL    |                |
| tasktime | varchar(255) | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

类别表

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| catid       | int(11)      | NO   | PRI | NULL    | auto_increment |
| catname     | varchar(255) | YES  |     | NULL    |                |
| userid      | int(11)      | NO   | PRI | 0       |                |
| countoftask | int(11)      | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

Answer 1

您不需要将值提取到变量中，但这不会导致错误。这是一个更简单的形式:

DELIMITER $$
CREATE TRIGGER tr_task_forinsert
AFTER INSERT ON task for each row
BEGIN
    UPDATE category 
    SET countoftask = countoftask + 1 
    WHERE catid = NEW.catid and userid = NEW.userid;
END;
$$
DELIMITER ;

您的问题可能只是分隔符。