自动更新数据库中的字段

Question

我想用另一列的聚合自动更新数据库列。 涉及三个表：

T_RIDER
  RIDER_ID
  TMP_PONYLIST
  ...

T_RIDER_PONY
  RIDER_ID
  PONY_ID

T_PONY
  PONY_ID
  PONY_NAME
  ...

T_RIDER 和 T_PONY 通过 T_RIDER_PONY 具有 n:m 关系。 T_RIDER 和 T_PONY 还有一些列，但只有 TMP_PONYLIST 和 PONY_NAME 与此处相关。

TMP_PONYLIST 是一个以分号分隔的 PONY_NAMES 列表，想象一下类似 “Twisty Tail;Candy Cane;Lucky Leaf” 的内容。 无论 T_RIDER_PONY 或 T_PONY 发生什么情况，我都希望使该字段保持最新状态。

我的第一个想法是在 T_RIDER_PONY 和 T_PONY 上设置触发器。 问题是似乎无法在触发器内读取 T_RIDER_PONY，我总是得到 ORA-04091。我发现了一些有关使用三个触发器和包变量的提示，但这听起来太复杂了。

也许您认为我最好更改架构或完全摆脱 TMP_PONYLIST。 这些是选项，但不是这个问题的主题。 目前我只对不需要对我的应用程序进行任何更改的答案感兴趣（没有应用程序直接使用表，只能使用视图，因此允许使用视图进行欺骗）。

那么，如何让 TMP_PONYLIST 自动保持最新状态？ 如何连接字符串是一个有趣的子问题，我还没有找到一个优雅的解决方案。

我正在使用 Oracle 数据库 10g 企业版版本 10.2.0.4.0 - 64bi。

更新

我喜欢使用物化视图的想法。 我所拥有的是：

CREATE MATERIALIZED VIEW
  V_TMP_PONYLIST 
BUILD IMMEDIATE
REFRESH COMPLETE ON COMMIT
AS SELECT 
  R.RIDER_ID, string_agg(P.PONY_NAME) AS TMP_PONYLIST
FROM
  T_PONY P, T_RIDER R, T_RIDER_PONY RP
WHERE 
  P.PLOTGROUP_ID=RP.PLOTGROUP_ID AND
  R.QUEUE_ID=RP.QUEUE_ID 
GROUP BY R.RIDER_ID;

string_agg 未显示，因为它很长并且我认为它不相关。

它不会使用 ON COMMIT 进行编译，我得到 ORA-12054。 据我了解，文档聚合仅被禁止 刷新速度快，那么这里有什么问题呢？

更新 文森特和托尼的答案不同，但都很有帮助。 我接受了托尼的，但也请务必阅读文森特的回答。

Answer 1

TMP_PONYLIST 列上的信息是多余的（它存在于其他地方）。您将遇到维护它的各种问题（除非有某种锁定机制，否则任何解决方案都无法在多用户环境中正常工作）。

在标准化模型中，您只需从物理模型中删除此列即可。如果您需要这些信息，可以使用视图，例如 Oracle 11gR2：

CREATE OR REPLACE VIEW rider_v AS
SELECT rider_id, /*...,*/
       (SELECT listagg(p.pony_name, ';') WITHIN GROUP (ORDER BY p.pony_name)
          FROM t_pony p
          JOIN t_rider_pony rp ON (p.pony_id = rp.pony_id)
         WHERE rp.rider_id = r.rider_id) tmp_ponylist
  FROM t_rider r;

请参阅 此 SO 例如 11gR2 之前的字符串聚合。

Answer 2

为什么需要在触发器中读取T_RIDER_PONY表？您将插入或删除一行，因此触发器需要做的就是在 T_PONY 中查找小马名称并更新表 T_RIDER，将名称附加到 TMP_PONYLIST 或从 TMP_PONYLIST 中删除它，如下所示

create trigger t_rider_pony_trg 
after insert or delete on t_rider_pony
for each row
begin
   if inserting then
      select pony_name
      into   l_pony_name
      where  pony_id = :new.pony_id;

      update t_rider
      set    tmp_ponylist = tmp_ponylist || ';' || l_pony_name
      where  rider_id = :new.rider_id;
   elsif deleting then
      select pony_name
      into   l_pony_name
      where  pony_id = :old.pony_id;

      update t_rider
      set    tmp_ponylist = ltrim ( 
                               rtrim ( 
                                  replace(';' || tmp_ponylist || ';',
                                          ';' || l_pony_name || ';',
                                          ';'),
                                  ';', ';')
      where  rider_id = :old.rider_id;
   end if;
end;

删除部分中的更新是相当的我承认这令人不愉快；最好使用 apex_util.string_to_table 和 apex_util.table_to_string 等实用程序来处理它！有关详细信息，请参阅此答案。