MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test (
  id INT PRIMARY KEY,
  chip_id INT,
  status CHAR(1),
  mod_status_date_time DATETIME
  );
INSERT INTO test VALUES
(1,      123,    'w',    '2024-02-07 11:20:06'),
(2,      456,    'i',    '2024-02-07 11:21:00');

Records: 2  Duplicates: 0  Warnings: 0


CREATE TRIGGER tr_bi_test
BEFORE INSERT ON test
FOR EACH ROW
BEGIN
    IF NEW.status = ( SELECT status 
                      FROM test 
                      WHERE chip_id = NEW.chip_id 
                      ORDER BY mod_status_date_time DESC LIMIT 1
                      ) THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Last ''status'' for this ''chip_id'' is the same, insertion rejected.',
          MYSQL_ERRNO = 1062;
    ELSE
        SET NEW.mod_status_date_time = CURRENT_TIMESTAMP;
    END IF;
END
-- If we have no row with the new chip_id 
-- we will Insert chip_id, status, now()
INSERT IGNORE INTO test VALUES
(3,      789,    'w',    '2024-02-07 12:20:06');
-- If we have a row with the chip_id AND new status 
-- is NOT the same as In the Latest (MOD_STATUS_DATE_TIME) 
-- we will Insert chip_id, status, now()
INSERT IGNORE INTO test VALUES
(4,      123,    'x',    '2024-02-07 13:20:06');
-- If we have a row with the chip_id AND new status
-- is the same as In the Latest (MOD_STATUS_DATE_TIME)
-- we will skip and do nothing
INSERT IGNORE INTO test VALUES
(5,      123,    'x',    '2024-02-07 14:20:06');

Last 'status' for this 'chip_id' is the same, insertion rejected.


SHOW WARNINGS;


Level
Code
Message


Error
1062
Last 'status' for this 'chip_id' is the same, insertion rejected.


SELECT * FROM test;


id
chip_id
status
mod_status_date_time


1
123
w
2024-02-07 11:20:06

2
456
i
2024-02-07 11:21:00

3
789
w
2024-02-07 13:38:51

4
123
x
2024-02-07 13:38:51

id	chip_id	status	mod_status_date_time
1	123	w	2024-02-07 11:20:06
2	456	i	2024-02-07 11:21:00
3	789	w	2024-02-07 13:38:51
4	123	x	2024-02-07 13:38:51