Я сейчас веду долгую борьбу за триггер DELETE в Oracle, который при удалении строки выбирает новое значение MAX из оставшихся строк и записывает его в другую таблицу. Наткнувшись на надоедливую ошибку мутирующей таблицы ORA-04091 (не могу прочитать таблицу в FOR EACH ROW), я переключился на составной триггер Oracle.
Как мне лучше всего сохранить удаленные строки (несколько значений в строке, потому что дальнейшая проверка должна обновляться только в том случае, если удаленная оценка могла быть высокой, а не более низкой)? Я боюсь, что глобальная временная таблица может закончиться беспорядком, если несколько событий триггера пересекаются и, например. обновление рекордов запускается для «DeletedMatches», которые на самом деле не были удалены, но зарегистрированы триггерным событием «До».
Могу ли я создать таблицу, которая а) существует только локально в этом триггере б) может использоваться в 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;