delmatch oracle_Oracle Compound触发器 - 如何存储和“使用”已删除的行？ INDEX BY表？...

我正在为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;