CODEWITHSUNDEEP
mysqlsql
Chris
Chris

Reputation: 59541

Updating parent table from referencing child

I'm working on a webplatform from which a teacher can assign tasks to students. Each task contains at least one or more sub-tasks.

Task_data is a table linking a task to each student and may contain data for each student´s progress on said task.

Sub_task_data is a table linking the task-data to each sub-task and may contain data for each student´s progress on said sub-task.

I have the following table structures:

=====================            =====================
|    Task_data      |            |   Sub_task_data   |
=====================            =====================
|     (PK) id       |            |     (PK) id       |
|   (FK) task_id    |  1 ----- n | (FK) sub_task_id  |
|  (FK) member_id   |            | (FK) task_data_id |
|     updated_on    |            |    is_completed   |
=====================            =====================

The tables members, task and subtask are not illustrated as it's not necessary here.

I'm wondering if the updated_on row can somehow automatically be updated with CURRENT_TIMESTAMP whenever a row in the Sub_task_data table (with the relevant Foreign Key) gets updated?

Thanks.

Upvotes: 0

Views: 202

Answers (1)

Sameer Mirji
Sameer Mirji

Reputation: 2245

You can try this:

DELIMITER $$
CREATE TRIGGER update_time_taskdata 
AFTER UPDATE ON Sub_task_data FOR EACH ROW 
BEGIN 
    UPDATE Task_data
       SET updated_on = NOW()
     WHERE id = NEW.task_data_id;
END;
$$
DELIMITER ;

Refer CREATE TRIGGER manual for more information.

Upvotes: 2

Related Questions