必须相互更新的两个表的触发器替代方案

（很抱歉这篇文章很长，但我想所有信息都是非常必要的）

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

Tables

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)
• 任务的结束日期和持续时间不能直接更改（感谢上帝！）

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

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

目前的方法

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

这（有点）有效，但有几个缺点：

• 如果几个人同时更改同一任务的数据，我们可能会得到不一致的数据（请参阅向 Tom 询问触发器问题 http://www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html)
• 子任务表更新后，我们会在很短的时间内出现数据不一致的情况（直到下次同步作业运行为止）。目前，我们在 GUI 中每次更改操作后手动运行作业，但这显然容易出错

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

Package

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;

Triggers

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;

Job

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;

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

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

您应该：

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

2. 可能比您的情况中的过程更好：使用不包含重复数据的架构。您不想存储冗余数据：这会使应用程序开发比所需的更加复杂。就绩效、资源和精力而言，解决问题的最佳方法是当您意识到该任务是不必要的时。

   根据模型的描述，以下是您可以删除的列：

   • 任务.duration_in_days
   • 任务.结束日期
   • 任务.needs_recomputation
   • 子任务.start_date
   • 子任务.结束日期

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

3. Use a 突变触发器解决方法 http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551198119097816936使用包变量来识别修改的行BEFORE and AFTER语句触发器。显然，这将涉及大量难以编码、测试和维护的代码，因此您应该尽可能使用选项 (1) 和 (2)。