Oracle复合触发器 - 如何存储和“使用”已删除的行？ INDEX BY表？

Question

我正在为Oracle中的DELETE触发器而战，该触发器在删除一行时从剩余行中选择一个新的最大值，并将其写入另一个表中。在遇到令人烦恼的ORA-04091 mutating table错误（在FOR EACH ROW中无法读取表）后，我切换到了Oracle的Compound Trigger。

如何最好地存储已删除的行（每行有多个值，因为进一步检查只会在删除的分数可能是高分时更新，而不是如果它是低分）？如果多个触发器事件交叉触发，例如对于未实际删除但由Before触发器事件注册的“DeletedMatches”，高分更新可能会导致全局临时表混乱。

我能否创建一个表来： a）仅在此触发器中本地存在 b）可以像普通DB表或临时表一样在SQL中使用

以下（伪）代码将在删除比赛时更新CurrentHighScores表（旧高分消失并被剩余最高分替换）。

CREATE TABLE GameScores (
    MatchId number not null --primary key
    Player  varchar(255) not null,
    Game    varchar(255) not null, -- PacMan, Pong, whatever...
    Score   number not null );

-- High score for each game:
CREATE TABLE CurrentHighScores (
    HiScId number not null --primary key
    Player  varchar(255) not null,
    Game    varchar(255) not null,
    HighScore   number not null );

create or replace TRIGGER UpdHiScoreOnMatchDelete
FOR DELETE ON GameScores 
COMPOUND TRIGGER
    TYPE matchtable IS TABLE OF GameScores%ROWTYPE INDEX BY SIMPLE_INTEGER;
    DeletedMatches matchtable;
    MatchIndex SIMPLE_INTEGER := 0;

BEFORE EACH ROW IS -- collect deleted match scores
BEGIN
  MatchIndex:= MatchIndex+ 1;
  DeletedMatches(MatchIndex).Game := :old.Game;
  DeletedMatches(MatchIndex).Score := :old.Score;
  -- don't want to set every column value, want to 
      -- do like: INSERT :old INTO DeletedMatches;
      -- don't want the Index either!
END BEFORE EACH ROW;

AFTER STATEMENT IS
BEGIN
    UPDATE CurrentHighScores hsc 
    SET hsc.HighScore=(
      select max(gsc.Score) from GameScores gsc
      where hsc.Game=gsc.Game)
    where hsc.Game IN (
      select del.Game from DeletedMatches del where hsc.HighScore = del.Score)
      -- won't work, how can I check within the SQL if a row 
              -- for this game has been deleted, or anyhow integrate 
              -- DeletedMatches into the SQL, without a cursor?
              -- Optional further cond. in subselect, to update only 
              -- if deleted score equals highscore: 
    and exists(
      select 1 from GameScores where Game=hsc.Game); 
      -- ignore games without remaining match scores.

    -- Delete/set zero code for games without existing scores omitted here.
END AFTER STATEMENT;

- Erik Hart

---

似乎带有ON COMMIT DELETE ROWS选项（默认）的全局临时表的内容仅限于正在进行的事务，因此我认为可以使用它。然而，这并不推荐。但是我还不知道如何在SQL查询中与普通表一起使用示例代码中使用的嵌套表。无论如何，触发器必须将某个分组的剩余行的最大值写入目标表（这里是：CurrentHighScores），实际上，该目标是一个元数据表，其数据大多与数据模型无关。DELETE通常只影响单个行，很少影响几百行。 - Erik Hart

Answer 1

“烦人的”可变表错误几乎总是指出了糟糕的设计，通常是非规范化的数据模型。在这种情况下似乎也是如此。如果需要维护聚合值、计数、最大值等，为什么不使用Oracle内置的功能呢？Oracle给我们提供了MATERIALIZED VIEW对象来处理摘要信息。了解更多。

在您的情况下，请使用材料化视图替换CurrentHighScores。

CREATE MATERIALIZED VIEW CurrentHighScores 
BUILD IMMEDIATE
REFRESH FAST
as select 
( 
    Player , 
    Game    , 
    max(score) as HighScore  
from GameScores 
group by player, game ; 

您需要在GameScores上构建一个MATERIALIZED VIEW LOG。

- APC

---

谢谢，但我不能将真实表格（已更改为CurrentHighScores以在此处发布）更改为视图，因为它包含来自各种来源的大量不同数据。数据库基本上是“原样”，如果我添加了一个视图，我仍然需要复制该值。复合触发器很有前途，但我需要一个良好的临时存储来存储使用BEFORE EACH ROW迭代的删除行，既可以在触发器事件中使用，也可以在SQL中使用。 - Erik Hart

物化视图基本上是一个表而不是视图。它是一个物理数据存储：区别在于数据维护在主表中。另一方面，视图只是一个存储的查询。 - APC

Answer 2

对于这种情况，全局临时表完成了任务。在每个行之前的游标中收集所有旧的行，在语句之后，将临时表与删除表连接并查找已删除项的新MAX值。

我担心在全局临时表中的触发器条目会干扰其他触发器事件的条目，就像MSSQL #tempTable一样常见，但我的担忧是错误的，ON COMMIT DELETE ROWS运行良好。

只是遗憾的是，在MSSQL中可以在几个小时内完成如此简单的触发器任务（包括测试），而在Oracle背景下需要太多的时间。在Oracle SQL Developer中，您可能会轻易地花费数小时来查找一些奇怪、难以理解的错误消息，其中包含不正确的行号（至少要有可见行作为参考），最终发现原因是缺少行结束分号或未正确关闭代码块。

材料化视图可能是未来的选择，我的问题是：matview数据是否具有事务性（例如由触发器进行的更改），还是延迟报告功能？我知道它可以被配置为定期更新，但也可以使用“快速”更新，几乎立即更新。然而，这带来了更令人讨厌的限制（例如，如果要搜索MAX值，则不能使用Where子句）。

- Erik Hart