（很长的帖子，很抱歉，但是我想所有信息都非常有必要）

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

桌子

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
);

商业规则

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

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

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

目前的方法

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

这种（种类）有效，但是它有几个缺点：

• 如果几个人同时更改同一任务的数据，我们可能会获得不一致的数据（有关触发器问题，请参见AskTom）
• 在子任务表上进行更新后，我们会在很短的时间内出现数据不一致的情况（直到下一次同步作业运行）。当前，我们在GUI中的每个更改操作之后手动运行作业，但这显然容易出错

所以我的问题是-是否有任何明智的替代方法？

包裹

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;