I have 5 columns in SQL table
Id | activity | start date | Finish date | Predecessor activity
1 | shuttering | 1/1/2019 | 3/1/2019 |
2 | concrete | | 6/1/2019 | 1
The above is an example of my table I need to plan my activities and all the activities are interlinked The ID 2 succeeds activity 1 So start date of ID 2 is finish date of 1 I need all the start dates to be auto generated except first one based on predecessor activity
Based on this table
CREATE TABLE Schedule1
(`Id` int, `activity` varchar(10), `start_date` datetime
, `Finish_date` datetime, `Predecessor_activity` int)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO Schedule1
(`Id`, `activity`, `start_date`, `Finish_date`, `Predecessor_activity`)
VALUES
(1, 'shuttering', '2019-01-01', '2019-03-01 01:00:00', NULL),
(2, 'concrete', NULL, '2019-06-01 02:00:00', 1),
(3, 'concrete', NULL, '2019-08-01 02:00:00', 2),
(4, 'concrete', NULL, '2019-10-01 02:00:00', 3);
This sql statemenbt
UPDATE Schedule1 t2
Left JOIN Schedule1 t1 ON t2.Predecessor_activity = t1.Id
SET t2.start_date = IFNULL(t2.start_date,t1.Finish_date);
gets you
Id activity start_date Finish_date Predecessor_activity
1 shuttering 2019-01-01 00:00:00 2019-03-01 01:00:00
2 concrete 2019-03-01 01:00:00 2019-06-01 02:00:00 1
3 concrete 2019-06-01 02:00:00 2019-08-01 02:00:00 2
4 concrete 2019-08-01 02:00:00 2019-10-01 02:00:00 3
for the use of trigger like insert or update like this
#DELIMITER //
DROP TRIGGER IF EXISTS my_update_trigger;
CREATE DEFINER=root@localhost TRIGGER my_update_trigger
AFTER update ON `schedule1`
FOR EACH ROW
BEGIN
-- Call the common procedure ran if there is an INSERT or UPDATE on `table`
-- NEW.id is an example parameter passed to the procedure but is not required
-- if you do not need to pass anything to your procedure.
CALL procedure_to_run_schedule()//
END//
DELIMITER ;
We nee a new Table, because updating the same table isn't allowed for triggers. So we create a new table that is identical to our original
DROP TABLE IF EXISTS newschedul;
CREATE TABLE newschedule SElect * From Schedule1;
And then we add a new stored procedure. What it does, After you inserted or updated your original table, it copies all data to the new table and uses above statement to make the new schedule. so you can access the actual schedule by selecting rows from the. Updfating and isnert you only make in the schedule to avoid the problmes you described
DELIMITER //
DROP PROCEDURE IF EXISTS procedure_to_run_schedule//
CREATE DEFINER=root@localhost PROCEDURE procedure_to_run_schedule()
READS SQL DATA
BEGIN
SET SQL_SAFE_UPDATES = 0;
DELETE FROM newschedule;
INSERT INTO newschedule
SELECT * FROM Schedule1;
UPDATE newschedule t2
Left JOIN newschedule t1 ON t2.Predecessor_activity = t1.Id
SET t2.start_date = IFNULL(t2.start_date,t1.Finish_date) ;
SET SQL_SAFE_UPDATES = 1;
END//
DELIMITER
