时间戳

首页 > 解决方案 > ORA-04091 表正在变异

oracle - ORA-04091 表正在变异

问题描述

我有以下 2 个表：运行：

+--------+-------------+
| run_id | status      |
+========+=============+
| 1      | active      |
+--------+-------------+
| 2      | new         |
+--------+-------------+

和命令：

+----------+--------+--------------+
| order_id | run_id | order_status |
+==========+========+==============+
| 1        | 1      | finished     |
+----------+--------+--------------+
| 2        | 1      | finished     |
+----------+--------+--------------+
| 3        | 1      | active       |
+----------+--------+--------------+
| 4        | 2      | new          |
+----------+--------+--------------+
| 5        | 2      | active       |
+----------+--------+--------------+
| 6        | 2      | active       |
+----------+--------+--------------+

要求实现如下逻辑：当运行中的所有订单状态相同时，应更新运行状态（与其订单相同）。

例如，当order_id = 3状态设置为时'finished'，run_id=1状态也应设置为'finished'。与order_id = 4将状态设置'active'为时相同。run_id = 2 should'active'

负责检查订单状态并相应更新运行状态的程序：

CREATE OR REPLACE PROCEDURE  check_and_update_run_status  (in_order_id     IN   orders.order_id%TYPE,
                                                           in_run_id       IN   runs.run_id%TYPE,
                                                           in_order_status IN   orders.order_status%TYPE)
AS
   v_update_run VARCHAR2(1) := 'N';
BEGIN
   /*query the table ORDERS and check if all orders in the given in_run_id having the same status as in_order_status: */
  SELECT CASE
           WHEN NOT EXISTS ( SELECT *
                                 FROM ( SELECT order_id,
                                               order_status
                                          FROM orders
                                         WHERE run_id = in_run_id )
                                WHERE order_status <> in_order_status )
             THEN 'Y'
           END
    INTO v_update_run
    FROM dual;

    IF v_update_run THEN
      UPDATE runs
         SET run_status = in_order_status
       WHERE run_id = in_run_id;
    END IF;

END check_and_update_run_status;

我已经创建了触发器

CREATE OR REPLACE TRIGGER trigger1 
AFTER INSERT OR UPDATE OF order_status ON orders FOR EACH ROW 
BEGIN
  check_and_update_run_status( in_order_id  => :new.order_id,
                               in_run_id    => :new.run_id,
                               in_po_status => :new.order_status );
END;

逻辑失败，因为错误：ORA-04091: table ORDERS is mutating, trigger/function may not see it。触发器正在调用一个过程，该过程查询调用触发器的同一个表。

解决此类问题的最佳方法是什么？

标签： oracletriggersmutating-tableora-04091

解决方案

还有其他方法可以解决mutating trigger，但我会尝试利用compound trigger. 话虽如此，如果可能，我们应该尽量避免triggers，在您的情况下，在更新表中的order status列期间在其他程序单元或应用程序代码中调用该过程，orders因为我看到这里的每一行都没有依赖关系，我们需要更新反对run_id而不是order_id。

话虽如此，我对过程进行了一些更改，因为在这个用例中我们不需要 order_id 参数

CREATE OR REPLACE PROCEDURE check_and_update_run_status
(
   in_run_id       IN runs.run_id%TYPE
  ,in_order_status IN orders.order_status%TYPE
) AS
   v_update_run VARCHAR2(1) := 'N';
BEGIN
   /*query the table ORDERS and check if all orders in the given in_run_id having the same status as in_order_status: */
   SELECT CASE
             WHEN NOT EXISTS (SELECT *
                   FROM   (SELECT order_id
                                 ,order_status
                           FROM   orders
                           WHERE  run_id = in_run_id)
                   WHERE  order_status <> in_order_status) THEN
              'Y' ELSE 'N'
          END
   INTO   v_update_run
   FROM   dual;

   IF v_update_run = 'Y'
   THEN
      UPDATE runs 
         SET status = in_order_status 
       WHERE run_id = in_run_id;
   END IF;

END check_and_update_run_status;
/

并创建compound trigger并调用该过程，

CREATE OR REPLACE TRIGGER trigger1
   FOR INSERT OR UPDATE OF order_status ON orders
   COMPOUND TRIGGER

   --table type to store the status index by run_id value
   TYPE table_a_row_data_t IS TABLE OF orders.order_status%TYPE INDEX BY PLS_INTEGER;

   -- global variable for the compound trigger
   g_row_level_data table_a_row_data_t;

   AFTER EACH ROW IS
   BEGIN
      IF NOT g_row_level_data.exists(:new.run_id)
      THEN
         g_row_level_data(:new.run_id) := :new.order_status;
      END IF;
   END AFTER EACH ROW;

   AFTER STATEMENT IS
   BEGIN
      --loop through all run_id and update the status by calling the procedure
      --here I used collection.first..collection.last as the index is run_id itself  
      FOR runid IN g_row_level_data.first .. g_row_level_data.last
      LOOP
         check_and_update_run_status(in_run_id    => runid
                                    ,in_order_status => g_row_level_data(runid));
      END LOOP;
   END AFTER STATEMENT;
END trigger1;
/

请测试，看看它是否符合您的要求。

推荐阅读