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`))
Best Answer
Thanks Michael for your help!
Good to know that @variables are users variables and without @ are local variables.
The following snipped worked well