您的位置: 首页  >  技术问答  >  仅当另一个表上的列被更新或新记录添加到另一个表时，才将记录插入新的MySQL数据库表中？

仅当另一个表上的列被更新或新记录添加到另一个表时，才将记录插入新的MySQL数据库表中？

分类: 技术问答 • 2022-03-12 14:35:23
问题描述：

I have the MySQL SQL below that is ran on my Project Management application I am building every time Project Tasks are Saved.

A save can consist of saving new tasks records as well as updating existing records. For this reason I have this fancy SQL below which Inserts a New Tasks record if one doesn't exist yet with that ID, if it does already exist then it updates certain fields. It is smart enough that it does NOT update the date_modified column unless name ,description ,status, type, priority fields have changed from their previous value. The reason for this is because the sort_order column will change on every record update but should not constitute a "change" in the date_modified field if only the sort_order column is updated.

The reason it works this way is because there is 1 form on a page that loads all the Tasks records for a project, so you can mass edit all the records at one time as well as add new Tasks records at the bottom. So this SQL below gets called in a WHILE loop for every Tasks record being saved.

It works amazing so far, just as I had hoped but now I am at another challenge.

I would like to create a new Database table for Updates which will basically insert a new record every time a NEW Tasks record is added and every time a Task record is UPDATED.

An example would be when I update a page with 20 Project Tasks and I change the values on 5 of the Tasks records, it would then insert 5 new records into my updates table. Something like...

JasonDavis Updated Task Record #1
JasonDavis Updated Task Record #2
JasonDavis Updated Task Record #3
JasonDavis Updated Task Record #4
JasonDavis Updated Task Record #5
JasonDavis ADDED NEW Task Record #4534

With the way my SQL is ran below, I am not able to know in my PHP which records were actually Updated as it updated every single record, I need to insert into my new table, only records where the date_modified is Updated.

QUESTION 1): Does anyone have any ideas how I might achieve this goal? Modifying my SQL below or another method to make sure I insert a new record into an UPDATES DB Table when a New Tasks record is created, OR when a Task record has ANY of these columns Updated: name ,description ,status, type, priority, or date_modifiedis UPDATED. This means if a Task record updates only a field like sort_order then it will NOT insert a new record into the UPDATES table for that updated Task record.

I have seen some posts on * that do somewhat similar but still far less complex items using TRIGGERS which I have never used a Trigger or even heard of them until recently so I have no idea if that is how I would HAVE to do it and if it is, how I would make it do such a complex conditional insert into my new UPDATES table.

My ultimate goal is to simply create a new UPDATES table which will act as a stream showing which tasks are created and modified and by which user and the DATETIME the event happened. What complicates it is my existing INSERT or UPDATE SQL below has to continue working how it does now but somehow get the correct Tasks that are UPDATED accordingly to the conditions mentioned above to constitute a new insert record into the new UPDATES table. Please help experts!?

$sql = "
    INSERT INTO
        project_tasks(task_id, project_id, created_by_user_id, modified_user_id, name, description, status, priority, type, date_entered, date_modified, date_started, date_completed, sort_order, heading)
    VALUES
        ('$taskId', '$projectId', '$created_by_user_id', '$modified_user_id', '$name', '$description', '$status', '$priority', '$type', UTC_TIMESTAMP(), UTC_TIMESTAMP(), '$date_started', '$date_completed', '$sort_order', '$heading')
    ON DUPLICATE KEY UPDATE
        date_modified = (CASE
            WHEN name <> values(name)
            OR description <> values(description)
            OR status <> values(status)
            OR type <> values(type)
            OR priority <> values(priority)
              THEN UTC_TIMESTAMP()
              ELSE date_modified
        END),
        modified_user_id='$modified_user_id',
        name='$name',
        description='$description',
        status='$status',
        priority='$priority',
        type='$type',
        date_started='$date_started',
        date_completed='$date_completed',
        sort_order='$sort_order',
        heading='$heading'";

 return $this->db->query($sql);

答

I started experimenting with Triggers and they seem to be my ticket, they are working and doing my end goal!

For Task INSERTS I have this trigger... 

DROP TRIGGER IF EXISTS project_task_new_stream;
CREATE TRIGGER `project_task_new_stream` AFTER INSERT ON `apoll_web_projects_tasks`
FOR EACH ROW
    INSERT INTO apoll_web_projects_updates_stream (event_type, record_id, modified_user_id, record_name, description, date_entered) VALUES ('0', NEW.task_id, NEW.modified_user_id, NEW.name, NEW.description, NEW.date_modified);

For Task UPDATES I have this trigger...which only creates an update stream record IF the date_modified field is changed! 

DROP TRIGGER IF EXISTS project_task_updates_stream;
DELIMITER $$
CREATE TRIGGER `project_task_updates_stream` AFTER UPDATE ON `apoll_web_projects_tasks` FOR EACH ROW
BEGIN
IF NOT (NEW.date_modified <=> OLD.date_modified)
    THEN
        INSERT INTO apoll_web_projects_updates_stream (event_type, record_id, modified_user_id, record_name, description, date_entered) VALUES ('0', NEW.task_id, NEW.modified_user_id, NEW.name, NEW.description, NEW.date_modified);
    END IF;
END $$
DELIMITER ;

相关推荐