我正在为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;
2 回答
"annoying"变异表错误几乎总是表示设计不佳,通常是非规范化数据模型 . 这似乎适用于这种情况 . 您需要维护聚合值,计数,最大值等,为什么不使用Oracle的内置功能? Oracle专门为我们提供了MATERIALIZED VIEW对象来处理摘要 . Find out more .
在您的情况下,用物化视图替换CurrentHighScores .
您还需要在GameScores上构建一个MATERIALIZED VIEW LOG .
对于这种情况,全球临时表完成了这项工作 . 收集所有:BEFORE EACH ROW游标中的旧行,然后在AFTER STATEMENT中将临时表与删除表连接,并找到项目被删除的新MAX值 .
我担心在全局临时触发条目会混淆其他触发事件的条目,就像MSSQL #tempTable一样,是错误的,ON COMMIT DELETE ROWS工作正常 .
遗憾的是,这样一个简单的触发任务,在MSSQL中完成几个小时(包括测试),花费了太多时间,阅读所有Oracle背景 . 在Oracle SQL Developer中,人们可以轻松地查看一些奇怪的,含有错误行号的神秘错误消息(至少以可见行作为参考),但却发现原因是缺少行结束分号或未正确关闭块 .
物化视图可能是未来的选择,我的问题是:是matview数据事务(如触发器所做的更改),还是延迟报告功能?我知道它可以配置为定期更新,但至少几乎立即更新“快速”更新 . 然而,这有更令人讨厌的限制(例如,如果要搜索MAX值,则没有Where子句) .