I am trying to update a column named ‘patient_los’ by putting date difference of two columns i.e. ‘ward_date’ and ‘discharge_date’. And this only happens when ‘discharge_date’ is change from null value to new value. But it is not working!
my trigger code is as follows:
CREATE DEFINER=`root`@`localhost` TRIGGER `update_ipd_los` AFTER UPDATE ON `ipd` FOR EACH ROW
BEGIN
if new.discharge_date<>OLD.discharge_date then
update ipd SET new.patient_los=DATEDIFF(new.discharge_date,ward_date)
WHERE keycode=NEW.keycode;
END if;
END
this should have updated the column ‘patient_los’. keycode is id as primary key