zAg4fE1J

1USE `myTask`;
2DELIMITER $$
3CREATE TRIGGER `t_taskaction_AUPD` AFTER UPDATE ON `t_taskaction` FOR EACH ROW
4BEGIN
5    DECLARE idTask INT(11);
6    DECLARE idTaskState INT(11);
7
8    set @idTask = NEW.idTask;
9
10    set @idTask = (SELECT idTaskState 
11    FROM t_task WHERE idTask = @idTask);
12
13    UPDATE t_task 
14    SET idTaskState = @idTaskState
15    WHERE idTask = @idTask;
16END$$
17DELIMITER ;
18	
19CREATE TABLE IF NOT EXISTS `myTask`.`t_task` (
20  `idTask` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
21  `task` VARCHAR(100) NOT NULL,
22  `idTaskState' INT(11) UNSIGNED NOT NULL DEFAULT 1,
23  `description` LONGTEXT NOT NULL,
24  `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
25  PRIMARY KEY (`idTask`)
26)
27
28CREATE TABLE IF NOT EXISTS `myTask`.`t_taskAction` (
29  `idTaskAction` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
30  `idTask` INT(11) NOT NULL,
31  `fecha` DATETIME NOT NULL,
32  `action` VARCHAR(45) NOT NULL,
33  `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
34  PRIMARY KEY (`idTaskAction`, `idTask`),
35  CONSTRAINT `t_ifocTareaAccion2t_ifocTarea`
36    FOREIGN KEY (`idTaskAction`)
37    REFERENCES `myTask`.`t_task` (`idTask`)
38    ON DELETE CASCADE
39    ON UPDATE CASCADE)
40
41CREATE TABLE IF NOT EXISTS `myTask`.`a_taskState` (
42  `idTaskState` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
43  `order` INT(11) NULL DEFAULT NULL,
44  `state` VARCHAR(45) NOT NULL,
45  `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
46  PRIMARY KEY (`idTaskState`))
47	
48USE `myTask`;
49DELIMITER $$
50CREATE DEFINER=`root`@`localhost` TRIGGER `t_taskaction_AUPD` 
51AFTER UPDATE ON `t_taskaction` 
52FOR EACH ROW
53BEGIN
54    DECLARE _idTask INT(11);
55    DECLARE _idTaskState INT(11);
56
57    set _idTask = NEW.idTask;
58
59    set _idTask = (SELECT idTaskState 
60    FROM t_task WHERE idTask = _idTask);
61
62    UPDATE t_task 
63    SET timestamp = now()
64    WHERE idTask = _idTask;
65END$$
66DELIMITER ;