I am fighting a long struggle now for a DELETE trigger in Oracle, which, upon deleting a line, selects a new MAX value from the remaining rows and writes it to another table. After stumbling across the annoying ORA-04091 mutating table error (can't read table while in FOR EACH ROW), I switched to Oracle's Compound Trigger.
How can I best store the deleted rows (multiple values per row, because the further check shall update only if the deleted score might have been the high score, not if it was a lower score)? I'm afraid a global temp table might end in a mess if multiple trigger events cross-fire and e.g. a highscore update runs for "DeletedMatches" which have not actually been deleted, but registered by the Before trigger event.
Can I create a table which a) exists only locally in this trigger b) can be used in SQL like normal DB tables or temporary tables ?
The following (pseudo-) code shall update the CurrentHighScores table whenever a match has been deleted (old high scores disappear and are replaced by the highest remaining score).
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;
