CODEWITHSUNDEEP
mysql
ANMOL JAIN
ANMOL JAIN

Reputation: 43

Update date column only when a particular column is updated

I need to automatically update the column value of "time_updated" only when "image" column is updated. If another column is updated the "time_updated" column should remain unchanged. 

CREATE TABLE `person` (

  `image` varchar(255) DEFAULT NULL,
  `address` varchar(1000) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `time_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Upvotes: 1

Views: 90

Answers (1)

P.Salmon
P.Salmon

Reputation: 17640

I am not sure why @Tim Biegeleisen thinks a trigger would not work but a before trigger would seem appropriate

drop table if exists t;
create table t (

  `image` varchar(255) DEFAULT NULL,
  `address` varchar(1000) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `time_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

drop trigger if exists t;
delimiter $$
create trigger t before update on t
for each row 
begin
    if old.image <> new.image then
        set new.time_updated = now();
    end if; 
end $$
delimiter ;

insert into t (image,address,email,time_updated) values
('aa','bb','cc','2019-01-01 01:01:01');

update t set image = 'ab' where image = 'aa';

select * from t;



+-------+---------+-------+---------------------+
| image | address | email | time_updated        |
+-------+---------+-------+---------------------+
| ab    | bb      | cc    | 2019-06-28 10:21:01 |
+-------+---------+-------+---------------------+
1 row in set (0.00 sec)

Upvotes: 1

Related Questions