CODEWITHSUNDEEP
mysqltriggers
Lic
Lic

Reputation: 1957

Recursive update trigger

I've got this mysql table:

CREATE TABLE IF NOT EXISTS `activities` (
`id`    INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
`name`  CHAR(255)   NOT NULL,
`status`    ENUM('open','progress','close'),
`date_begin`    DATE,
`date_finish`   DATE,
`progress` TINYINT,
`reliance` INTEGER,
`parent`    INTEGER,
FOREIGN KEY(owner) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
FOREIGN KEY(reliance) REFERENCES activities(id) ON UPDATE CASCADE ON DELETE SET NULL,
FOREIGN KEY(parent) REFERENCES activities(id) ON UPDATE CASCADE ON DELETE SET NULL
)ENGINE = INNODB;

My problem is when i want to update the date_begin of one activity. Infact i would like to update the date of begin of all activities that are reliant or child of the updated activity. Can i force mysql to create a recursive trigger?

Upvotes: 0

Views: 2534

Answers (2)

ckinfos
ckinfos

Reputation: 121

Although the trigger may work...it is not a good practice to create recursive triggers! and if u thing you must... then think not only twice but 100times before applying! They can result in more harm than good sometimes.

Upvotes: 1

Russell Fox
Russell Fox

Reputation: 5435

Something like this should work, at least if you're on a recent version of MySQL:

delimiter |

CREATE TRIGGER activitiesUpdateReliantAndChildren BEFORE UPDATE ON 'activities'
FOR EACH ROW 
BEGIN 

update 'activities' 
set date_begin = NEW.date_begin 
where reliance = NEW.id;

update 'activities' 
set date_begin = NEW.date_begin 
where parent = NEW.id;

END;
|

delimiter ;

Upvotes: 1

Related Questions