问题:当更新学生成绩表SCORE中的学生成绩时,自动计算该学生的平均成绩保存到学生表SAVG字段中。代码:CREATE OR REPLACE TRIGGER SCORE_SCORE
AFTER INSERT OR UPDATE OR DELETE ON SCORE
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
DECLARE
AVG NUMBER;
BEGIN
IF INSERTING THEN
SELECT AVG(SCORE) INTO AVG FROM SCORE
GROUP BY SNO HAVING SNO=:NEW.SNO;
UPDATE STUDENT
SET SAVG=AVG
WHERE SNO=:NEW.SNO;
ELSE
SELECT AVG(SCORE) INTO AVG FROM SCORE
GROUP BY SNO HAVING SNO=:OLD.SNO;
UPDATE STUDENT
SET SAVG=AVG
WHERE SNO=:OLD.SNO;
ENDIF;
COMMIT;
END;
触发器创建成功了,但是当我修改和插入数据的时候,总是提示:SCORE表发生了变化, 触发器/函数不能读它。刚学orcale 求大神指点。谢谢!
AFTER INSERT OR UPDATE OR DELETE ON SCORE
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
DECLARE
AVG NUMBER;
BEGIN
IF INSERTING THEN
SELECT AVG(SCORE) INTO AVG FROM SCORE
GROUP BY SNO HAVING SNO=:NEW.SNO;
UPDATE STUDENT
SET SAVG=AVG
WHERE SNO=:NEW.SNO;
ELSE
SELECT AVG(SCORE) INTO AVG FROM SCORE
GROUP BY SNO HAVING SNO=:OLD.SNO;
UPDATE STUDENT
SET SAVG=AVG
WHERE SNO=:OLD.SNO;
ENDIF;
COMMIT;
END;
触发器创建成功了,但是当我修改和插入数据的时候,总是提示:SCORE表发生了变化, 触发器/函数不能读它。刚学orcale 求大神指点。谢谢!
CREATE OR REPLACE TRIGGER SCORE_SCORE
AFTER INSERT OR UPDATE OR DELETE ON SCORE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
Pragma autonomous_transaction;
AVG NUMBER;
BEGIN
IF INSERTING THEN
begin
SELECT AVG(SCORE)
INTO AVG
FROM SCORE
WHERE SNO = :NEW.SNO;
UPDATE STUDENT SET SAVG = AVG WHERE SNO = :NEW.SNO;
exception
when others then
UPDATE STUDENT SET SAVG = :NEW.SCORE WHERE SNO = :NEW.SNO;
end;
ELSE
SELECT AVG(SCORE)
INTO AVG
FROM SCORE
WHERE SNO = :OLD.SNO;
UPDATE STUDENT SET SAVG = AVG WHERE SNO = :OLD.SNO;
END IF;
COMMIT;
END;
改成这样试试