Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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`))
share|improve this question
1  
Your code contains numerous errors, including confusion and ambiguity between local variables (not beginning with @), column names (which are being ignored because you've declared local variables of the same name), and user defined variables (beginning with @). –  Michael - sqlbot Feb 6 at 18:03
1  
...and even if the errors weren't there, it appears as if all it would do would be to update values to what they already are, which would not cause auto-timestamps to be updated, since data updating a value to what it already holds doesn't change ON UPDATE CURRENT_TIMESTAMP values. –  Michael - sqlbot Feb 6 at 18:05
    
ok, I got it. I've seen some examples but any of them help me out with this. So I tried to update directly timestamp = now() and I will do so! Thanks for all your help. ;) –  Joe Feb 6 at 18:16
    
I already fixed it! But the trigger is executed even if the values modified are the same in the update. –  Joe Feb 6 at 18:17
1  
There isn't much that's unique to triggers that isn't applicable to all stored programs, other than the OLD and NEW aliases, which is what you need to test, to avoid acting on an update that doesn't change interesting columns... IF NOT (OLD.column_name <=> NEW.column_name) THEN trigger actions here END IF; ... the NOT <=> test is the same as != except that it is also true when both sides of the comparison are null, which is not the case for != which evaluates as null when either side is null. –  Michael - sqlbot Feb 6 at 19:09
show 1 more comment

1 Answer

up vote 0 down vote accepted

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 ;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.