CODEWITHSUNDEEP
mysqlsql
user3789570
user3789570

Reputation: 9

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?

Upvotes: 0

Views: 72

Answers (2)

Peter Henell
Peter Henell

Reputation: 2466

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

Upvotes: 1

jarlh
jarlh

Reputation: 44795

Have the display column removed. (It has no own data, just a copy of other columns' data.)

Create a view instead:

create view viewname as
select id, code, descr, ccode, cdescr, count,
       concat('(', code, ')-(', ccode ')-(', cast(count as varchar(11)), ')') as display
from tablename

Will always return up-to date values in the display column!

Upvotes: 0

Related Questions