触发必须相互更新的两个表的替代方案 [英] Trigger alternatives for two tables that have to mutually update each other

问题描述

(很长的帖子，很抱歉，但是我想所有信息都确实有必要)

(Sorry for the long post, but I guess all the information is really necessary)

我们有两个表-任务和子任务.每个任务由一个或多个子任务组成，并且每个对象都有开始日期，结束日期和持续时间.此外，子任务具有顺序.

We have two tables - task and subtask. Each task consists of one or more subtasks, and each of these objects has a start date, end date and duration. Additionally, subtasks have a ordering.

表格

create table task (
  pk number not null primary key, 
  name varchar2(30) not null,
  start_date date,
  duration_in_days number,
  end_date date,
  needs_recomputation number default 0
);

create table subtask (
  pk number not null primary key, 
  task_fk references task(pk),
  name varchar2(30) not null,
  start_date date,
  duration_in_days number,
  end_date date,
  ordering number not null
);

业务规则

• 第一个子任务与任务的开始日期相同
• 对于每个后续子任务，其开始日期等于其前任的结束日期
• 最后一个子任务的结束日期与任务相同
• 对于每个子任务和任务:开始日期+持续时间=结束日期
• 任务: duration = sum(子任务的持续时间)
• 任务的结束日期和持续时间无法直接更改(感谢上帝！)

这直接为更新/删除生成以下要求:

This directly generates the following requirements for updates/deletes:

• 更改任务的开始日期时，将其第一个子任务的开始日期设置为相同的值，并重新计算所有子任务的开始日期和结束日期
• 更改子任务的开始日期，结束日期或持续时间时，其其他字段也会相应地更新，随后的所有子任务也会相应地更新，最后，任务也会相应地更新
• 删除子任务后，所有后续子任务都会相应更新，最后，任务也会相应更新

当前方法

• 任务表中有一个触发器，可以在开始日期更改时更新第一个子任务并设置needs_recomputation标志
• 子任务表具有一个触发器，该触发器可以使开始日期/结束日期/持续时间保持一致，并为父任务设置Needs_recomputation标志(由于变异表问题，我们无法在此处直接更新后续任务)
• 为避免触发级联，每个触发器都设置一个包变量，以指示不应触发其他触发器
• dbms_scheduler作业定期检查任务表，并重新计算设置了Needs_recomputation标志的任务的数据

(某种)可行，但有一些缺点:

This (kind of) works, but it has several drawbacks:

• 如果几个人同时为同一任务更改数据，我们可能会获得不一致的数据(请参阅所以我的问题是-有什么明智的替代方法吗?

  So my question is - is there any sensible alternative approach for this?

  包装

  create or replace package pkg_task is
  
    g_update_in_progress boolean;
    procedure recomputeDates(p_TaskID in task.pk%TYPE);
  
    procedure recomputeAllDates;
  end;
  
  create or replace package body pkg_task is
  
    procedure recomputeDates(p_TaskID in task.pk%TYPE) is
    begin
      g_update_in_progress := true;
      -- update the subtasks
      merge into subtask tgt
      using (select pk,
                    start_date,
                    duration_in_days,
                    end_date,
                    sum(duration_in_days) over(partition by task_fk order by ordering) as cumulative_duration,
                    min(start_date) over(partition by task_fk) + sum(duration_in_days) over(partition by task_fk order by ordering rows between unbounded preceding and 1 preceding) as new_start_date,
                    min(start_date) over(partition by task_fk) + sum(duration_in_days) over(partition by task_fk order by ordering) as new_end_date
               from subtask s
              where s.task_fk = p_TaskID
              order by task_fk,
                       ordering) src
      on (src.pk = tgt.pk)
      when matched then
        update
           set tgt.start_date = nvl(src.new_start_date,
                                    src.start_date),
               tgt.end_date   = nvl(src.new_end_date,
                                    src.end_date);
      -- update the task                                  
      merge into task tgt
      using (select p_TaskID as pk,
                    min(s.start_date) as new_start_date,
                    max(s.end_date) as new_end_date,
                    sum(s.duration_in_days) as new_duration
               from subtask s
              where s.task_fk = p_TaskID) src
      on (tgt.pk = src.pk)
      when matched then
        update
           set tgt.start_date          = src.new_start_date,
               tgt.end_date            = src.new_end_date,
               tgt.duration_in_days    = src.new_duration,
               tgt.needs_recomputation = 0;
      g_update_in_progress := false;
    end;
  
    procedure recomputeAllDates is
    begin
      for cur in (select pk
                    from task t
                   where t.needs_recomputation = 1)
      loop
        recomputeDates(cur.pk);
      end loop;
    end;
  
  begin
    g_update_in_progress := false;
  end;
  

  触发器

  create or replace trigger trg_task
  before update on task
  for each row
    begin
      if (:new.start_date <> :old.start_date and not pkg_task.g_update_in_progress) then
        pkg_task.g_update_in_progress := true;
        -- set the start date for the first subtask
        update subtask s 
        set s.start_date = :new.start_date
        where s.task_fk = :new.pk
        and s.ordering = 1;
        :new.needs_recomputation := 1;
        pkg_task.g_update_in_progress := false;      
      end if;
    end;
  
  create or replace trigger trg_subtask
    before update on subtask
    for each row
  declare
    l_date_changed boolean := false;
  begin
    if (not pkg_task.g_update_in_progress) then
      pkg_task.g_update_in_progress := true;
  
      if (:new.start_date <> :old.start_date) then
        :new.end_date  := :new.start_date + :new.duration_in_days;
        l_date_changed := true;
      end if;
      if (:new.end_date <> :old.end_date) then
        :new.duration_in_days := :new.end_date - :new.start_date;
        l_date_changed        := true;
      end if;
      if (:new.duration_in_days <> :old.duration_in_days) then
        :new.end_date  := :new.start_date + :new.duration_in_days;
        l_date_changed := true;
      end if;
  
      if l_date_changed then
        -- set the needs_recomputation flag for the parent task
        -- if this is the first subtask, set the parent's start date, as well
        update task t
           set t.start_date         =
               (case
                 when :new.ordering = 1 then
                  :new.start_date
                 else
                  t.start_date
               end),
               t.needs_recomputation = 1
         where t.pk = :new.task_fk;
      end if;
      pkg_task.g_update_in_progress := false;
    end if;
  end;
  

  工作

  begin
    dbms_scheduler.create_job(
        job_name => 'JOB_SYNC_TASKS'
       ,job_type => 'PLSQL_BLOCK'
       ,job_action => 'begin pkg_task.recomputeAllDates; commit; end; '
  
       ,start_date      => to_timestamp_tz('2014-01-14 10:00:00 Europe/Berlin',
                                           'yyyy-mm-dd hh24:mi:ss tzr')
       ,repeat_interval => 'FREQ=HOURLY;BYMINUTE=0,5,10,15,20,25,30,35,40,45,50,55'
       ,enabled => TRUE
       ,comments => 'Task sync job, runs every 5 minutes');
  end;
  

  推荐答案

  在这里使用触发器只是自找麻烦.

  Using triggers here is just asking for trouble.

  此外，选择使用调度程序可能不是最好的主意，因为调度的作业只能看到已提交的数据.因此，要么您要提交将事务逻辑抛出窗口之外的触发器，要么将对表的更改延迟到事务结束为止.

  Furthermore, the choice of using the scheduler is probably not the best idea since the scheduled jobs can only see committed data. So either you're committing in the trigger which throws transaction logic out of the window or the changes to the tables are delayed until the end of the transaction.

  您应该:

  1. 使用过程.最简单的答案.当您有多个应用程序时，它们不应直接执行DML/业务逻辑，而应始终使用过程来执行它们，以便它们都运行相同的代码.禁止使用授权或视图的直接DML.您可能需要通过视图上的 INSTEAD OF 触发器强制使用过程(仅当您无法修改应用程序时才考虑使用此过程.)

  1. Use procedures. The simplest answer. When you have multiple applications they should not perform DML/businees logic directly, they should always do it with procedures so that they all run the same code. Forbid direct DML with grants or views. You may need to force the use of procedures through INSTEAD OF triggers on views (consider this only if you can't modify the application).

  可能比您所使用的过程还要好:使用不包含重复数据的模式.您不想存储冗余数据:这使应用程序开发比所需的更为复杂.就性能，资源和精力而言，解决问题的最佳方法是当您意识到任务是不必要的.

  Probably even better than procedures in your case: use a schema that doesn't contain duplicate data. You don't want to store redundant data: this makes application development more complex than needed. In terms of performace, resources and energy, the best way to solve a problem is when you realize that the task is unnecessary.

  在模型说明中，以下是您可以删除的列:

  From the description of your model, here are the columns that you could remove:

  • task.duration_in_days
  • task.end_date
  • task.needs_recomputation
  • subtask.start_date
  • subtask.end_date

  
  task 表将仅包含开始日期，并且每个子任务将仅存储其持续时间.当您需要汇总信息时，请使用联接.您可以使用视图使应用程序透明地访问数据.

  
  The task table would contain the start date only, and each subtask would only store its duration. When you need the aggregate information, use joins. You can use views to let the applications access the data transparently.

  使用更改触发器变通办法打包变量，以使用 BEFORE 和 AFTER 语句触发器来标识已修改的行.显然，这将涉及大量难以编写，测试和维护的代码，因此应尽可能使用选项(1)和(2).

  Use a mutating trigger workaround that uses package variables to identify modified rows with BEFORE and AFTER statement triggers. Obviously this will involve lots of code that will be hard to code, test and maintain so you should use options (1) and (2) whenever possible instead.

  这篇关于触发必须相互更新的两个表的替代方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！