CODEWITHSUNDEEP
oracle-databasetriggersdelete-rowora-04091
Erik Hart
Erik Hart

Reputation: 1324

Oracle Compound trigger - how to store and "use" deleted rows? INDEX BY table?

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;

Upvotes: 3

Views: 3350

Answers (2)

Erik Hart
Erik Hart

Reputation: 1324

For this case, the Global Temporary Table did the job. Collect all :old rows in the BEFORE EACH ROW cursor, then, in the AFTER STATEMENT, join the temp table with the delete table and find the new MAX values for where items habe been deleted.

My fear that trigger entries in the global temp would mess with entries from other trigger events, as commonly with a MSSQL #tempTable, was wrong, ON COMMIT DELETE ROWS works fine.

Just a shame that such a simple trigger task, to be completed in a few hours in MSSQL (including testing), took WAY too much time, reading into all the Oracle background. And in Oracle SQL Developer, one can easily look hours after some strange, cryptic error messages with incorrect line numbers (at least with the visible lines as reference), only to find that the reason was a missing line end semicolon or a not correctly closed block.

Materialized views may be a future option, my question would be: is the matview data transactional (like changes done by a trigger), or is it a delayed reporting function? I know it can be configured to update periodically, but also with "Fast" update at least almost immediately. This, however, with more annoying restrictions (such as no Where clauses if MAX values are to be searched).

Upvotes: 1

APC
APC

Reputation: 146249

The "annoying" mutating table error almost always indicates poor design, usually a denormalised data model. That would seem to apply in this case. In you need to maintain aggregate values, counts, maximums, etc why not use Oracle's built in functionality? Oracle gave us the MATERIALIZED VIEW object specifically to handle summaries. Find out more.

In your case replace CurrentHighScores with a materialized view.

CREATE MATERIALIZED VIEW CurrentHighScores 
BUILD IMMEDIATE
REFRESH FAST
as select 
( 
    Player , 
    Game    , 
    max(score) as HighScore  
from GameScores 
group by player, game ;

You'll need to build a MATERIALIZED VIEW LOG on GameScores as well.

Upvotes: 5

Related Questions