How do I keep my “validity” row always updated in relation to my “expiration date” row?

This is my database’s survey table:

I learned to use CURDATE() and DATEADD(), so the table can now automatically calculate the survey_date and its expiration_date.

But I’d also like to add a value to my validity column.
My validity column is a BIT(1).
I’d like 1 to represent a Valid Survey, and a 0 to represent an Invalid Survey.

How do I make it so that the validity column is always correct in relationship to my expiration_date column?
I want it to change depending on the date, so it’s correct all the time, instead of only being correct when I first insert the row.

Thanks!

Answer

It would be nice if you could add this using a generated column

alter table survey
    add validity bit generated always as (expiration_date < now());

However, that is not allowed, because now() is a volatile function (i.e., it changes value every time it is called).

You need to use a view:

create view v_survey as 
    select s.*,
           (expiration_date < now()) as is_valid
    from survey;