基于触发表之和更新表的Oracle触发器

l5tcr1uw 于 2023-05-28 发布在 Oracle

关注(0)|答案(1)|浏览(152)

我有以下表格数据结构：

PARENT
ID      TOTAL_AMOUNT
101     10
102     15

CHILDREN
ID    PARENT_ID  AMOUNT
201   101        6
202   101        4
203   102        10
204   102        5

我想创建一个触发器，当修改CHILDREN时，它将PARENT.TOTAL_AMOUNT更新为其子记录的总和。但我似乎无法绕过“表正在变异”的限制。
使用Oracle 11.
编辑：
下面是重现上述示例场景的代码。

CREATE TABLE PARENTS (
  ID NUMBER PRIMARY KEY,
  TOTAL_AMOUNT NUMBER
);

CREATE TABLE CHILDREN (
  ID NUMBER PRIMARY KEY,
  PARENT_ID NUMBER REFERENCES PARENTS(ID),
  AMOUNT NUMBER
);

INSERT INTO PARENTS( ID, TOTAL_AMOUNT ) VALUES ( 101, 10 );
INSERT INTO PARENTS( ID, TOTAL_AMOUNT ) VALUES ( 102, 15 );

INSERT INTO CHILDREN ( ID, PARENT_ID, AMOUNT ) VALUES ( 201, 101, 6 );
INSERT INTO CHILDREN ( ID, PARENT_ID, AMOUNT ) VALUES ( 202, 101, 4 );
INSERT INTO CHILDREN ( ID, PARENT_ID, AMOUNT ) VALUES ( 203, 102, 10 );
INSERT INTO CHILDREN ( ID, PARENT_ID, AMOUNT ) VALUES ( 204, 102, 5 );

CREATE OR REPLACE TRIGGER TRG_TEST
  AFTER UPDATE OF AMOUNT ON CHILDREN
  FOR EACH ROW
BEGIN

  UPDATE PARENTS
  SET TOTAL_AMOUNT = (
    SELECT SUM(AMOUNT)
    FROM CHILDREN
    WHERE PARENT_ID = :NEW.PARENT_ID
  );
END;
/

运行以下语句会产生错误：

UPDATE CHILDREN SET AMOUNT = 11 WHERE ID = 204;

Error starting at line : 44 in command -
UPDATE CHILDREN SET AMOUNT = 11 WHERE ID = 204
Error report -
ORA-04091: table RMS.CHILDREN is mutating, trigger/function may not see it
ORA-06512: at "RMS.TRG_TEST", line 3
ORA-04088: error during execution of trigger 'RMS.TRG_TEST'

oracle

来源：https://stackoverflow.com/questions/76326134/oracle-trigger-for-updating-table-based-on-sum-of-triggering-table

1条答案

按热度按时间

yh2wf1be1#

最好使用VIEW或MATERIALIZED VIEW来生成总数。
如果你必须使用触发器（不要），那么你可以从使用AFTER ... EACH ROW改为使用AFTER语句触发器：

CREATE OR REPLACE TRIGGER TRG_TEST
  AFTER UPDATE OF AMOUNT ON CHILDREN
BEGIN
  UPDATE PARENTS p
  SET TOTAL_AMOUNT = (
    SELECT SUM(AMOUNT)
    FROM   CHILDREN c
    WHERE  c.PARENT_ID = p.ID
  );
END;
/

如果您希望触发器更有效，并且只更新已修改的id，则可以使用复合触发器。

CREATE OR REPLACE TRIGGER TRG_TEST
  FOR UPDATE OF AMOUNT ON CHILDREN
COMPOUND TRIGGER
  TYPE ids_type IS TABLE OF PARENTS.ID%TYPE;
  ids ids_type := ids_type();
AFTER EACH ROW
  IS
  BEGIN
    IF :OLD.parent_id IS NOT NULL AND :OLD.parent_id NOT MEMBER OF ids THEN
      ids.EXTEND;
      ids(ids.COUNT) := :OLD.parent_id;
    END IF;

    IF :NEW.parent_id IS NOT NULL AND :NEW.parent_id NOT MEMBER OF ids THEN
      ids.EXTEND;
      ids(ids.COUNT) := :NEW.parent_id;
    END IF;
  END AFTER EACH ROW;  
AFTER STATEMENT
  IS
  BEGIN
    FORALL i IN 1 .. ids.COUNT
      UPDATE parents
      SET   total_amount = (SELECT SUM(amount)
                            FROM   children
                            WHERE  parent_id = ids(i))
      WHERE id = ids(i);
  END AFTER STATEMENT;
END;
/

fiddle

赞(0）回复(0）举报 2023-05-28

我来回答

基于触发表之和更新表的Oracle触发器

1条答案

相关问题

热门标签

最新问答