CODEWITHSUNDEEP
mysqlsql
Poseidaan
Poseidaan

Reputation: 298

How to call `ON UPDATE CURRENT_TIMESTAMP()` without actually updating the row?

So, as things stand I have two tables related to this question

List, with columns id, name, modified and Content, with columns id, listId, value

When I change the values in Content (using either INSERT, UPDATE, or DELETE) I want the corresponding list's modified value to update.

I have set the modified column to have ON UPDATE CURRENT_TIMESTAMP() however, this does not do anything since the row in the list table is not updated.

Is it possible to make sql automatically call the list's on update when its contents are modified?

The best solution would be one where I can change some setting in phpmyadmin so that this is done automatically, if no such thing is possible I would love to hear what the most efficient way is to call this update anyhow using PDO.

Before anyone comes up with this, I am aware that I could call first a select on to get the name of the list, store that in a variable, modify the list's name so that its ON UPDATE is called, and then modify again to reinsert the original name. This, to me, seems like a horrible approach.

Upvotes: 0

Views: 276

Answers (2)

lbrandao
lbrandao

Reputation: 4373

Seems like a good use for an AFTER UPDATE TRIGGER. Check the following links:

https://www.mysqltutorial.org/mysql-triggers/mysql-after-update-trigger/

MySQL after update, update datetime column of same table

Upvotes: 1

Pule Maanela
Pule Maanela

Reputation: 65

Have you tried using the date() function instead of the CURRENT_TIMESTAMP?

Upvotes: 1

Related Questions