How to create trigger to update timestamp when record is updated or inserted in another table

Question

I have a table t_task and a table t_taskaction with a foreign key to t_task. I would like to update timestamp from t_task that is refering the foreign key form t_taskaction with every update in action.

It doesn't get any udpate in t_task table, and I'm not getting any error.

USE `myTask`;
DELIMITER $$
CREATE TRIGGER `t_taskaction_AUPD` AFTER UPDATE ON `t_taskaction` FOR EACH ROW
BEGIN
    DECLARE idTask INT(11);
    DECLARE idTaskState INT(11);

    set @idTask = NEW.idTask;

    set @idTask = (SELECT idTaskState 
    FROM t_task WHERE idTask = @idTask);

    UPDATE t_task 
    SET idTaskState = @idTaskState
    WHERE idTask = @idTask;
END$$
DELIMITER ;

Tables

CREATE TABLE IF NOT EXISTS `myTask`.`t_task` (
  `idTask` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `task` VARCHAR(100) NOT NULL,
  `idTaskState' INT(11) UNSIGNED NOT NULL DEFAULT 1,
  `description` LONGTEXT NOT NULL,
  `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`idTask`)
)

CREATE TABLE IF NOT EXISTS `myTask`.`t_taskAction` (
  `idTaskAction` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `idTask` INT(11) NOT NULL,
  `fecha` DATETIME NOT NULL,
  `action` VARCHAR(45) NOT NULL,
  `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`idTaskAction`, `idTask`),
  CONSTRAINT `t_ifocTareaAccion2t_ifocTarea`
    FOREIGN KEY (`idTaskAction`)
    REFERENCES `myTask`.`t_task` (`idTask`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)

CREATE TABLE IF NOT EXISTS `myTask`.`a_taskState` (
  `idTaskState` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `order` INT(11) NULL DEFAULT NULL,
  `state` VARCHAR(45) NOT NULL,
  `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idTaskState`))

Answer 1

Thanks Michael for your help!

Good to know that @variables are users variables and without @ are local variables.

The following snipped worked well

USE `myTask`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` TRIGGER `t_taskaction_AUPD` 
AFTER UPDATE ON `t_taskaction` 
FOR EACH ROW
BEGIN
    DECLARE _idTask INT(11);
    DECLARE _idTaskState INT(11);

    set _idTask = NEW.idTask;

    set _idTask = (SELECT idTaskState 
    FROM t_task WHERE idTask = _idTask);

    UPDATE t_task 
    SET timestamp = now()
    WHERE idTask = _idTask;
END$$
DELIMITER ;