By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (id INT,
name VARCHAR(255),
job VARCHAR(255),
jobUpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP);
INSERT INTO table_name VALUES (1, 'name 1', 'job 1', DEFAULT);
CREATE TRIGGER trigger_name
BEFORE UPDATE
ON table_name
FOR EACH ROW
SET NEW.jobUpdatedAt = CASE WHEN OLD.job = NEW.job
THEN OLD.jobUpdatedAt
ELSE CURRENT_TIMESTAMP
END;
-- Update name only
SELECT *, SLEEP(1) FROM table_name;
UPDATE table_name SET name = 'name 2' WHERE id = 1;
SELECT *, SLEEP(1) FROM table_name;
| id | name | job | jobUpdatedAt | SLEEP(1) |
|---|---|---|---|---|
| 1 | name 1 | job 1 | 2021-07-08 11:14:43 | 0 |
| id | name | job | jobUpdatedAt | SLEEP(1) |
|---|---|---|---|---|
| 1 | name 2 | job 1 | 2021-07-08 11:14:43 | 0 |
-- Update both
UPDATE table_name SET name = 'name 3', job = 'job 2' WHERE id = 1;
SELECT *, SLEEP(1) FROM table_name;
| id | name | job | jobUpdatedAt | SLEEP(1) |
|---|---|---|---|---|
| 1 | name 3 | job 2 | 2021-07-08 11:14:45 | 0 |
-- Update job only
UPDATE table_name SET job = 'job 3' WHERE id = 1;
SELECT *, SLEEP(1) FROM table_name;
| id | name | job | jobUpdatedAt | SLEEP(1) |
|---|---|---|---|---|
| 1 | name 3 | job 3 | 2021-07-08 11:14:46 | 0 |
-- fake update - old and new values are the same
UPDATE table_name SET name = 'name 3', job = 'job 3' WHERE id = 1;
SELECT *, SLEEP(1) FROM table_name;
| id | name | job | jobUpdatedAt | SLEEP(1) |
|---|---|---|---|---|
| 1 | name 3 | job 3 | 2021-07-08 11:14:46 | 0 |