douzhongpi9734 2014-10-01 22:21
浏览 70
已采纳

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

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 StackOverflow 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);
  • 写回答

1条回答 默认 最新

  • duankun9280 2014-10-01 23:57
    关注

    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 ;
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 python天天向上类似问题,但没有清零
  • ¥30 3天&7天&&15天&销量如何统计同一行
  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
  • ¥15 C#调用python代码(python带有库)
  • ¥15 矩阵加法的规则是两个矩阵中对应位置的数的绝对值进行加和
  • ¥15 活动选择题。最多可以参加几个项目?
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)