首页 文章

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

提问于
浏览
1

我正在为Oracle中的DELETE触发器进行长期斗争,在删除一行时,从剩余的行中选择一个新的MAX值并将其写入另一个表 . 在遇到烦人的ORA-04091变异表错误(在FOR EACH ROW中无法读取表)之后,我切换到了Oracle的复合触发器 .

如何最好地存储已删除的行(每行多个值,因为只有在删除的分数可能是高分时才进行进一步检查,而不是如果分数较低)?如果多个触发事件交叉发生,我担心全局临时表可能会一团糟 . “DeletedMatches”的高分更新运行实际上尚未删除,但由Before触发事件注册 .

我可以创建一个表,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;

2 回答

  • 4

    "annoying"变异表错误几乎总是表示设计不佳,通常是非规范化数据模型 . 这似乎适用于这种情况 . 您需要维护聚合值,计数,最大值等,为什么不使用Oracle的内置功能? Oracle专门为我们提供了MATERIALIZED VIEW对象来处理摘要 . Find out more .

    在您的情况下,用物化视图替换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 .

  • 1

    对于这种情况,全球临时表完成了这项工作 . 收集所有:BEFORE EACH ROW游标中的旧行,然后在AFTER STATEMENT中将临时表与删除表连接,并找到项目被删除的新MAX值 .

    我担心在全局临时触发条目会混淆其他触发事件的条目,就像MSSQL #tempTable一样,是错误的,ON COMMIT DELETE ROWS工作正常 .

    遗憾的是,这样一个简单的触发任务,在MSSQL中完成几个小时(包括测试),花费了太多时间,阅读所有Oracle背景 . 在Oracle SQL Developer中,人们可以轻松地查看一些奇怪的,含有错误行号的神秘错误消息(至少以可见行作为参考),但却发现原因是缺少行结束分号或未正确关闭块 .

    物化视图可能是未来的选择,我的问题是:是matview数据事务(如触发器所做的更改),还是延迟报告功能?我知道它可以配置为定期更新,但至少几乎立即更新“快速”更新 . 然而,这有更令人讨厌的限制(例如,如果要搜索MAX值,则没有Where子句) .

相关问题