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

Question

(对不起，帖子太长了，但我想所有的信息都是非常必要的)

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

表

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标志(由于变异表问题，我们不能在这里直接更新后续任务)

为避免触发器级联，每个触发器设置一个包变量以指示不应触发其他触发器

dbms_scheduler 作业定期检查任务表并重新计算设置了需要重新计算标志的任务的数据

这(某种)有效，但它有几个缺点:

如果几个人同时更改同一任务的数据，我们可能会得到不一致的数据(参见 AskTom on problems with triggers)

在子任务表上更新后，我们有很短的时间段数据不一致(直到下一次同步作业运行)。目前，我们在 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;

Answer 1

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

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

你应该:

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

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

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

task.duration_in_days

task.end_date

task.needs_recomputation

subtask.start_date

subtask.end_date

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

使用 mutating trigger workaround使用包变量来标识带有 BEFORE 的修改行和 AFTER语句触发器。显然，这将涉及大量难以编码、测试和维护的代码，因此您应该尽可能使用选项 (1) 和 (2)。