时间戳

首页 > 解决方案 > 尝试在表中插入一行并创建触发器后出现这些错误

oracle - 尝试在表中插入一行并创建触发器后出现这些错误

问题描述

我收到这些错误：

INSERT INTO "DANIEL"."STATION" (NUM_STATION, NOM_STATION, ALTITUDE, REGION) 
VALUES ('6', 'Baalbak', '1250', 'Baalbek')
ORA-04091: table DANIEL.STATION is mutating, trigger/function may not see it
ORA-06512: at "DANIEL.TRUPDATE_HIST_STATION", line 5
ORA-04088: error during execution of trigger 'DANIEL.TRUPDATE_HIST_STATION'
ORA-06512: at line 1


One error saving changes to table "DANIEL"."STATION":
Row 6: ORA-04091: table DANIEL.STATION is mutating, trigger/function may not 
see it
ORA-06512: at "DANIEL.TRUPDATE_HIST_STATION", line 5
ORA-04088: error during execution of trigger 'DANIEL.TRUPDATE_HIST_STATION'
ORA-06512: at line 1

在表站中插入一行后

这是我刚刚编写的触发器代码：

create or replace TRIGGER trupdate_hist_station
  AFTER INSERT ON STATION
  FOR EACH ROW
DECLARE
  V_num_station NUMBER;
BEGIN
  BEGIN
    SELECT s.NUM_STATION
      INTO v_num_station
      FROM STATION s
      WHERE s.NUM_STATION = :NEW.NUM_STATION;
  EXCEPTION
    WHEN no_data_found THEN
      v_num_station :=0;
  END;

 INSERT INTO HIST_STATION
    ("NUM_STATION","ANNEE","NB_RESERV","REVENU")
  VALUES
    (V_num_station,(SELECT to_char(SYSDATE,'YYYY')FROM DUAL),0,0);
END trupdate_hist_station;

在表 Station 中添加一行后，我只需要在 Hist_table 中添加一个新行

标签： oracleplsql

解决方案

不需要SELECT您插入的表中的任何内容（这就是导致突变的原因）。

此外，去掉双引号，它们不是必需的，因为列名默认为大写。

此外，不要SELECT一年dual-从SYSDATE.

这应该这样做：

create or replace TRIGGER trupdate_hist_station
  AFTER INSERT ON STATION
  FOR EACH ROW
BEGIN
 INSERT INTO HIST_STATION  
    (num_station, annee, nb_reserv, revenu)
 VALUES
    (:new.num_station, extract (year from sysdate), 0, 0);
END trupdate_hist_station;

推荐阅读