SQL 触发器执行期间出错 - ORA-06512 - ORA-04088

Error during trigger execution - ORA-06512 - ORA-04088

提问by user2999664

I've these tables

我有这些桌子

CELL(CellId, x0, y0, x1, y1, CurrentPhone#, MaxCalls)
TELEPHONE(PhoneNo, x, y, PhoneState)
STATE_CHANGE(ChangeId, TimeStamp, PhoneNo, x, y, ChangeType)

CELL(CellId, x0, y0, x1, y1, CurrentPhone#, MaxCalls)
TELEPHONE(PhoneNo, x, y, PhoneState)
STATE_CHANGE(ChangeId, TimeStamp, PhoneNo, x, y, ChangeType)

And I've to create this trigger (the trigger is mandatory)

我必须创建这个触发器（触发器是强制性的）

Changing the maximum number of active calls: The maximum number of active calls related to a single cell may be reduced by the cellular phone network for managing issues (decrease of theMaxCalls value in theCELL table). The update on theMaxCalls attribute for a single cell could cause an inconsistent situation in which theMaxCalls value in theCELL table becomes smaller than the number of currently Active phones(PhoneState='Active') in the considered cell. If so, the correspondingMaxCalls attribute needs to be updated with the number of currently Active phones(PhoneState='Active') in the considered cell

更改最大活动呼叫数：蜂窝电话网络可能会减少与单个小区相关的最大活动呼叫数以管理问题（减少CELL表中的MaxCalls值）。单个单元格的MaxCalls属性更新可能会导致不一致的情况，其中CELL表中的MaxCalls值变得小于所考虑单元格中当前活动电话的数量(PhoneState='Active') 。如果是这样，对应的MaxCalls属性需要更新为当前活动电话的数量（PhoneState='Active'）在考虑的单元格中

I wrote this trigger

我写了这个触发器

create or replace trigger CELL_T1
AFTER UPDATE OF MAXCALLS ON CELL
BEGIN
UPDATE CELL E1
SET E1.MAXCALLS=(
    SELECT COO
    FROM (SELECT E2.CELLID, COO
          FROM CELL E2, (
             SELECT CELLID, COUNT(*) COO
             FROM CELL C2, TELEPHONE
             WHERE PhoneState='Active' AND x<x1 AND x>=x0 AND y<y1 AND y>=y0
             GROUP BY C2.CellId
             )TW
         WHERE E2.CELLID=TW.CELLID AND COO>E2.MAXCALLS
         )
 )
WHERE  E1.CELLID IN (
SELECT C1.CELLID
FROM CELL C1, (
            SELECT CELLID, COUNT(*) COO
            FROM CELL C3, TELEPHONE
            WHERE PhoneState='Active' AND x<x1 AND x>=x0 AND y<y1 AND y>=y0
            GROUP BY C3.CellId
            )TW1
WHERE C1.CELLID=TW1.CELLID AND COO>C1.MAXCALLS and e1.cellid=tw1.cellid
         );    
END;?

The trigger compiled without a problem; then I wrote the update statement:

触发器编译没有问题；然后我写了更新语句：

UPDATE CELL SET MAXCALLS=MAXCALLS-2;

but I got these errors:

但我收到了这些错误：

ORA-06512: at "MATTEO.CELL_T1", line 2
ORA-04088: error during execution of trigger 'MATTEO.CELL_T1'
ORA-06512: at "MATTEO.CELL_T1", line 2
ORA-04088: error during execution of trigger 'MATTEO.CELL_T1'
ORA-06512: at "MATTEO.CELL_T1", line 2
ORA-04088: error during execution of trigger 'MATTEO.CELL_T1'
ORA-06512: at "MATTEO.CELL_T1", line 2
ORA-04088: error during execution of trigger 'MATTEO.CELL_T1'
ORA-06512: at "MATTEO.CELL_T1", line 2
ORA-04088: error during execution of trigger 'MATTEO.CELL_T1'
ORA-0

1. UPDATE CELL
2. SET MaxCalls = MaxCalls-2;

1. 更新单元格
2. SET MaxCalls = MaxCalls-2;

I can't find anything wrong in my trigger; what's going wrong?

我在触发器中找不到任何错误；怎么了？

回答by Ben

The problem is most likely that you're doing a recursive update here. When you execute your UPDATE your trigger does another UPDATE, which fires the trigger which does another UPDATE in a never ending cycle. Until, that is, Oracle gets bored and raises all those exceptions.

问题很可能是您在这里进行递归更新。当您执行您的 UPDATE 时，您的触发器会执行另一个 UPDATE，这会触发在永无止境的循环中执行另一个 UPDATE 的触发器。直到，也就是说，Oracle 感到无聊并引发所有这些异常。

In this situation there's really only two ways out.

在这种情况下，真的只有两条路。

• Don't UPDATE the table and use an INSTEAD OF DML triggeron a view (or something) so that the trigger only fires once.
• Remove all of this logic into a stored procedure and don't use a trigger at all.

• 不要更新表并在视图（或其他东西）上使用INSTEAD OF DML 触发器，以便触发器只触发一次。
• 将所有这些逻辑删除到存储过程中并且根本不使用触发器。

回答by Jan

While I know that the reason for this question (your university assignment) is not relevant anymore. Someone else might still have some similar problem.

虽然我知道这个问题的原因（你的大学作业）不再相关。其他人可能仍然有一些类似的问题。

• If you need consistency checks in triggers do them BEFORE the update, not afterwards.
• Only check the rows actually modified by your statement.
• For this kind of operation, use a ROW trigger, not a STATEMENT trigger
• Make use of the ":OLD" and ":NEW" datasets instead of querying the table

• 如果您需要在触发器中进行一致性检查，请在更新之前而不是之后进行。
• 只检查您的语句实际修改的行。
• 对于这种操作，使用 ROW 触发器，而不是 STATEMENT 触发器
• 使用 ":OLD" 和 ":NEW" 数据集而不是查询表

So do something like this (NOT tested in a database, only written on the fly)

所以做这样的事情（没有在数据库中测试，只在运行中编写）

CREATE OR REPLACE TRIGGER cell_t1
BEFORE UPDATE OF maxcalls ON cell
FOR EACH ROW
DECLARE
  v_active_cnt      NUMBER(20); 
BEGIN
  SELECT COUNT(*)
  INTO v_active_cnt
  FROM telephone
  WHERE phonestate='Active'
    AND x<:NEW.x1
    AND x>=:NEW.x0
    AND y<:NEW.y1
    AND y>=:NEW.y0;

  IF ( :NEW.maxcalls < v_active_cnt )
  THEN
    :NEW.maxcalls := v_active_cnt
  END;
END;

What this does is - Query the telephonetable for the number of active phones in the cell identified by the currently modified row (:NEW dataset) - Check if the modified maxcallsvalue is bigger than this number and if so modify the new maxcallsvalue accordingly

这样做是 - 查询telephone当前修改行（：新数据集）标识的单元格中活动电话数的表 - 检查修改后的maxcalls值是否大于此数字，如果是，则相应地修改新maxcalls值

This, by the way, is exactly what these kind of triggers are for.

顺便说一下，这正是这些触发器的用途。