Oracle DBMS-在处理AFTER触发器中的更新之前先读取表-突变表 [英] Oracle DBMS - Read a table before processing Updating in an AFTER trigger - mutating table

问题描述

过去几周我一直在尝试使用Oracle，但我偶然发现了一个似乎无法解决的问题.

I've been experimenting with Oracle over the past few weeks and I've stumbled upon an issue which I can't seem to wrap my head around.

我正在构建一个小型的资产管理系统，并且尝试在数据库端处理尽可能多的操作(纯粹是实验性的，我只是想在任何人问到之前都将其清除，为什么不通过以下方式更新这些行?客户")

I am building a small property management system and I am trying to process as many actions as possible on the database side (purely experimental, I just wanted to clear this up before anyone asks, "why dont you just update these rows through the client")

在我的系统中，我有一个属性和房间表(下面是简化的模式):

In my system, I have a properties and rooms table (simplified schema below):

`-------------------------------
`           PROPERTIES
`-------------------------------
`- PropertyID:          PK 
`- PropertyStatus:      VARCHAR
`------------------------------- 

`-------------------------------
`            ROOMS
`-------------------------------
`- RoomID:              PK 
`- PropertyID:          FK
`- RoomStatus:          VARCHAR
`------------------------------- 

无论何时将用户分配到房间，房间状态都会更新为OCCUPIED，一旦发生这种情况，我希望检查与属性n关联的房间数，如果所有房间都被占用，property_status应该更新到FULL，那么如果未从属性中分配用户，则值将更新为VACANCIES AVAILABLE等.

Whenever a user is assigned to a room the rooms status is updated to OCCUPIED, once this happens I wish to check how many rooms associated with property n are taken, if all rooms are taken the property_status should be Updated to FULL, then if users are unassigned from properties the value updates to VACANCIES AVAILABLE etc.

我对此具有基本的逻辑:

I have the basic logic for this mapped out:

-- Return how many vacant rooms belong to this property
CREATE OR REPLACE FUNCTION prop_vacancy_query(
   p_property_id       properties.property_id%TYPE
)
RETURN NUMBER
   IS 
v_prop_rooms NUMBER;
BEGIN
   SELECT COUNT(room_status) 
   INTO v_prop_rooms
   FROM rooms 
     JOIN properties ON
     rooms.property_id = properties.property_id
     WHERE room_status = 'VACANT'
     AND rooms.property_id = p_property_id;
  RETURN v_prop_rooms;
END prop_vacancy_query;

在我的Rooms表上的AFTER触发器中，我尝试调用查询，但收到一个变异表错误，我相信这是因为prop_vacancy_query正在读取属性表.

In my AFTER trigger on my rooms table I try to call the query but I get a mutating table error, I believe this is because prop_vacancy_query is reading the properties table.

CREATE OR REPLACE TRIGGER trg_rooms_after
AFTER INSERT OR UPDATE ON rooms FOR EACH ROW
BEGIN
-- Update the table based on the result
IF prop_vacancy_query(:NEW.property_id) = 0 THEN
    UPDATE properties
    SET prop_status = 'VACANT'
    WHERE properties.property_id = :NEW.property_id;
ELSE
    UPDATE properties
    SET prop_status = 'FULL'
    WHERE properties.property_id = :NEW.property_id;
END IF;
END;

以前，该代码适用于我的系统，但是由于对pragma autonomous transactions进行了深入了解，所以我意识到，在自己的独立事务上运行prop_vacancy_query()是极其糟糕的做法.

Previously this code worked for my system, but since reading more into pragma autonomous transactions I have realised it was extremely bad practice to run the prop_vacancy_query() on its own independent transaction.

有什么方法可以读取属性表，然后更新rooms表，而不会发生变异错误?

Is there any way that I can read from the properties table and then update the rooms table without getting a mutating error?

推荐答案

仅说明一下，引发变异表异常是因为您试图从函数中的rooms表中读取，而不是因为您试图读取从properties表中.由于您在rooms上具有行级触发器，因此这意味着rooms表在行级触发器触发时处于更改中间，并且可能处于不一致状态.在这种情况下，Oracle会阻止您查询rooms表，因为结果不一定是确定性的或可重复的.

Just to clarify, the mutating table exception is thrown because you are trying to read from the rooms table in your function, not because you are trying to read from the properties table. Since you have a row-level trigger on rooms, that means that the rooms table is in the middle of a change when the row-level trigger is firing and that it may be in an inconsistent state. Oracle prevents you from querying the rooms table in that situation because the results are not necessarily deterministic or reproducible.

如果创建了语句级触发器(删除了FOR EACH ROW)并将逻辑放在此处，则您将再也不会遇到变异表异常，因为rooms表将不再处于不一致状态.但是，语句级触发器无法查看修改了哪些行.这意味着您需要查看所有属性以查看应调整哪些状态值.那并不是特别有效.

If you created a statement-level trigger (removing the FOR EACH ROW) and put your logic there, you would no longer encounter a mutating table exception because the rooms table would no longer be in an inconsistent state. A statement-level trigger, though, is not able to see which row(s) were modified. That would mean that you'd need to look across all properties to see which status values should be adjusted. That's not going to be particularly efficient.

以额外的复杂性为代价，可以通过捕获行级触发器中更改的属性，然后在语句级触发器中引用更改的属性来提高性能.通常，这需要三个触发器和一个包装，这显然会大大增加活动部件的数量(如果您使用的是11.2，则可以将复合触发器与三个组件触发器一起使用，从而通过消除使用包装的需求来简化操作) .看起来像

At the cost of additional complexity, you can improve the performance by capturing which properties changed in a row-level trigger and then referring to that in a statement-level trigger. That generally requires three triggers and a package, which obviously increases the number of moving pieces substantially (if you're on 11.2, you can use a compound trigger with three component triggers which simplifies things a bit by eliminating the need to use the package). That would look something like

CREATE OR REPLACE PACKAGE trigger_collections
AS
  TYPE modified_property_tbl IS TABLE OF properties.property_id%type;
  g_modified_properties modified_property_tbl;
END;

-- Initialize the collection in a before statement trigger just in case
-- there were values there from a prior run
CREATE OR REPLACE TRIGGER trg_initialize_mod_prop_coll
  BEFORE INSERT OR UPDATE ON rooms
BEGIN
  trigger_collections.g_modified_properties := trigger_collections.modified_property_tbl();
END;

-- Put the property_id of the modified row in the collection
CREATE OR REPLACE TRIGGER trg_populate_mod_prop_coll
  AFTER INSERT OR UPDATE ON rooms
  FOR EACH ROW
BEGIN
  trigger_collections.g_modified_properties.extend();
  trigger_collections.g_modified_properties( trigger_collections.g_modified_properties.count + 1 ) := :new.property_id;
END;

CREATE OR REPLACE TRIGGER trg_process_mod_prop_coll
  AFTER INSERT OR UPDATE ON rooms
BEGIN
  FOR p IN 1 .. trigger_collections.g_modified_properties.count
  LOOP
    IF prop_vacancy_query( trigger_collections.g_modified_properties(i) ) = 0 
    THEN
      ...
END;

这篇关于Oracle DBMS-在处理AFTER触发器中的更新之前先读取表-突变表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！