CODEWITHSUNDEEP
mysqltriggersmysql-workbench
SNM
SNM

Reputation: 6795

Triggers - How to change another column when one is updated - MySQL

I have been given a use case where an user needs to update a certain TIME field if other of the same type changes.

I have been given two columns

horario_arribo (TIME)

horario_salida (TIME)

Now, what I need to do is the followin

Make a trigger so when I change for example horario_arribo, horario_salida is the same TIME as horario_arribo minus 1 hour, and the same if I update horario_salida , make horario_arribo plus 1 hour.

I have been thinking how to do it, first check if the value trying to be updated is horario_salida, then I just add 1 hour to horario_arribo, or if I'm updating horario_arribo, to the same to horario_salida but minus 1 hour.

DELIMITER //
CREATE TRIGGER modificar_horarioruta AFTER
UPDATE ON ruta
FOR EACH ROW
BEGIN
IF(new.horario_salida) THEN
  SET AddTime(old.horario_arribo, '00:60:00')
ELSE
  SET AddTime(old.horario_salida, Here I dont know how to minus 1 hour to that)
END IF;
END //
DELIMITER ;

This is the data I have

enter image description here

So , in short, if I update horario_salida, horario_arribo need to be 1 hour ahead, if I update horario_arribo, horario_salida needs to be 1 hour less.

Thanks

Upvotes: 1

Views: 33

Answers (1)

Lord Elrond
Lord Elrond

Reputation: 16032

IIUC:

DELIMITER //
CREATE TRIGGER modificar_horarioruta AFTER
UPDATE ON ruta
FOR EACH ROW
BEGIN
IF (NEW.horario_salida) THEN
  SET NEW.horario_arribo = OLD.horario_arribo + 1;
ELSE
  SET NEW.horario_salida = OLD.horario_salida - 1;
END IF;
END //
DELIMITER ;

Let me know if this is not what you need.

Upvotes: 1

Related Questions