how to make a trigger that update counter when I insert a new value

Question

Goodmorning,

I have this two tables:

CREATE TABLE post (
    ID_post INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    titolo_post VARCHAR(255) DEFAULT NULL, 
    testo_post TEXT NOT NULL, 
    data_post TIMESTAMP NOT NULL,
    autore_post INT(10) UNSIGNED REFERENCES utente(ID_utente) ON UPDATE NO ACTION ON DELETE CASCADE, 
    blog_post INT(10) UNSIGNED REFERENCES blog(ID_blog) ON UPDATE NO ACTION ON DELETE CASCADE,
    conteggio_like_post INT(10) DEFAULT 0
);

CREATE TABLE feedback (
    ID_feedback INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    post_feedback INT(10) UNSIGNED REFERENCES post(ID_post) ON UPDATE NO ACTION ON DELETE CASCADE,
    utente_feedback INT(10) UNSIGNED REFERENCES utente(ID_utente) ON UPDATE NO ACTION ON DELETE CASCADE, 
    UNIQUE(post_feedback, utente_feedback)
);

I want to increment 'conteggio_like_post', that is the counter of feedback, when i insert a new row on the table of feedback.

I used this trigger:

CREATE TRIGGER updateLikeCounter AFTER INSERT ON feedback 
FOR EACH ROW  
update post set conteggio_like_post = COALESCE(conteggio_like_post, 0) + 1
where id_post = post_feedback

But when I try to insert new values, i receive this problem:

#1054 - Colonna sconosciuta 'post_feedback' in 'where clause'

It doesn't recognized the column 'post_feedback'. I use XAMPP for my database.

I don't know where is the problem on the trigger code.

Answer 1

USE NEW keyword to get the value like:

CREATE TRIGGER updateLikeCounter AFTER INSERT ON feedback 
FOR EACH ROW  
update post set conteggio_like_post = COALESCE(conteggio_like_post, 0) + 1
where id_post = NEW.post_feedback;

note: Make sure that a record is also created, since you are only doing one update. Otherwise use INSERT INTO ... ON DUPLICATE KEY ...

sample:

mysql> select * from post;
+---------+-------------+------------+---------------------+-------------+-----------+---------------------+
| ID_post | titolo_post | testo_post | data_post           | autore_post | blog_post | conteggio_like_post |
+---------+-------------+------------+---------------------+-------------+-----------+---------------------+
|      10 | a           | b          | 2024-05-20 08:52:10 |          11 |        12 |                  14 |
+---------+-------------+------------+---------------------+-------------+-----------+---------------------+
1 row in set (0.00 sec)

mysql> insert into feedback VALUES( 25,10,47);
Query OK, 1 row affected (0.01 sec)

mysql> select * from post;
+---------+-------------+------------+---------------------+-------------+-----------+---------------------+
| ID_post | titolo_post | testo_post | data_post           | autore_post | blog_post | conteggio_like_post |
+---------+-------------+------------+---------------------+-------------+-----------+---------------------+
|      10 | a           | b          | 2024-05-20 08:52:10 |          11 |        12 |                  15 |
+---------+-------------+------------+---------------------+-------------+-----------+---------------------+
1 row in set (0.00 sec)

mysql> 

trigger:

    mysql> CREATE TRIGGER updateLikeCounter AFTER INSERT ON feedback  
FOR EACH ROW   update post set conteggio_like_post = COALESCE(conteggio_like_post, 0) + 1 
where id_post = NEW.post_feedback;

Query OK, 0 rows affected (0.01 sec)