Trigger to insert on update of specific column and specific value mysql

Question

I have table users as

id | status | created_at | updated_at
----------------------------------------
1  | 0      | 2020-07-23 17:02:49 | 2020-07-23 17:02:49

2  | 0      | 2020-07-23 17:09:24 | 2020-07-23 17:09:24

Want to insert a record in new table when status update to 1 and that updated row id with count in status_count_logs

id | user_id | status_count | created_at
-----------------------------------------

status_count will be previous value + 1 for that specific user. I created a trigger for that status_count is not incrementing and how can I get users id to insert in trigger.

Example:

UPDATE `users` SET `status`='1' WHERE `id`='2';

When above query executed then new row should get inserted in status_count_logs with values as

user_id = 2, status_count = previous status_count for user_id + 1

status_count is not incrementing.

Below is my trigger

DELIMITER $$
DROP TRIGGER IF EXISTS STATUS_UPDATE_LOGS$$
CREATE TRIGGER STATUS_UPDATE_LOGS AFTER UPDATE ON `users`
FOR EACH ROW BEGIN
    DECLARE s_count integer;
    SET @s_count := (SELECT CASE WHEN MAX(status_count) IS NOT NULL THEN MAX(status_count) + 1 ELSE 0 + 1 END FROM status_count_log WHERE id = NEW.id);
    
  IF NEW.status = 1 THEN
    INSERT INTO status_count_log (user_id, status_count, created_at) VALUES (NEW.id, @s_count, NOW());
  END IF;
END$$
DELIMITER ;

Answer 1

WHERE id = NEW.id should be WHERE user_id = NEW.id

DROP TABLE IF EXISTS STATUS_COUNT_LOG;
CREATE TABLE STATUS_COUNT_LOG
(USER_ID INT,STATUS_COUNT INT,CREATED_AT DATETIME);

DELIMITER $$
DROP TRIGGER IF EXISTS T$$
CREATE TRIGGER T AFTER UPDATE ON `users`
FOR EACH ROW BEGIN
    #DECLARE status_count integer;
    SET @status_count := (SELECT CASE WHEN MAX(status_count) IS NOT NULL THEN MAX(status_count) + 1 
                                            ELSE 0 + 1 END 
                                  FROM status_count_log
                                  where user_id = new.id );
    
  IF NEW.status = 1 THEN
    INSERT INTO status_count_log (user_id,status_count, created_at) VALUES (new.id,@status_count, NOW());
  END IF;
END$$
DELIMITER ;
truncate table status_count_log;
UPDATE USERS SET STATUS = 1 WHERE ID = 1;
UPDATE USERS SET STATUS = 1 WHERE ID = 1;

SELECT * FROM STATUS_COUNT_LOG;

+---------+--------------+---------------------+
| USER_ID | STATUS_COUNT | CREATED_AT          |
+---------+--------------+---------------------+
|       1 |            1 | 2020-07-24 13:45:38 |
|       1 |            2 | 2020-07-24 13:45:38 |
+---------+--------------+---------------------+
2 rows in set (0.001 sec)