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

2 votes

1 answer

15445 views

mysql mysql-5.5 trigger timestamp row-modification-time

                          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))
                        

Asked by Joe (203 rep)

Feb 6, 2014, 05:56 PM
Last activity: Dec 21, 2016, 01:40 AM

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

Related Questions