CODEWITHSUNDEEP
mysqltimestamp
Gilad
Gilad

Reputation: 43

mysql - timestamp column of another column value change

Say I have a mySQL table of a few columns:

id, name, job, jobUpdatedAt

Whenever specifically the job column changes, I want mySQL to automatically update the timestamp of jobUpdatedAt. (so if only name changes, it does not update)

Is such thing possible?

Thanks

Upvotes: 0

Views: 50

Answers (1)

Akina
Akina

Reputation: 42632

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;

jobUpdatedAt will be renewed only when job is updated by fact.

jobUpdatedAt will be saved if the query should try to update it explicitly.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1b48bce653eb9a1d778e406fa5af894a

Upvotes: 1

Related Questions