1

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;
ANMOL JAIN
  • 43
  • 1
  • 7

1 Answers1

1

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)
P.Salmon
  • 17,104
  • 2
  • 12
  • 19
  • Thanks Salmon, it is working but when I am trying this to implement on a larger table ie. with more columns it isn't working. – ANMOL JAIN Jun 28 '19 at 10:51
  • CREATE TABLE `person` ( `fname` varchar(255) DEFAULT NULL, `sname` varchar(255) DEFAULT NULL, `image` varchar(255) DEFAULT NULL, `about` varchar(1000) DEFAULT NULL, `address` varchar(1000) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `experience` varchar(1000) DEFAULT NULL, `template` int(11) DEFAULT NULL, `color` varchar(255) NOT NULL DEFAULT 'green', `welcome` varchar(255) DEFAULT 'yes', `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `time_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); – ANMOL JAIN Jun 28 '19 at 10:54
  • You didn't remove ON UPDATE CURRENT_TIMESTAMP – P.Salmon Jun 28 '19 at 11:29