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