user3789570 user3789570 - 3 months ago 7
MySQL Question

Mysql updating row on value change

I have a table like following.(All are VARCHAR(255) except id and count which are INT)

--------------------------------------------------------
| id | code | descr | ccode | cdescr | count | display |
--------------------------------------------------------


I want to make display to have following format (code)-(ccode)-(count)
What I mean is display value should be consisting of values from same row
And I want to update this display whenever a change occurs in this row, I heard about triggers (although I don't have much knowledge about them), but also read something that you can't update a table you put trigger on. Is this true? If it is, how I'm going to handle my situation?

Answer

Consider making the Display column a Generated Column. Then the column will be part of the table, but only its value will only be generated when you read it.

VIRTUAL: Column values are not stored, but are evaluated when rows are read, immediately after any BEFORE triggers. A virtual column takes no storage.

https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html

Comments