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