Oracle SQL触发器上的突变表 [英] Mutating table on Oracle SQL trigger

问题描述

我正在尝试执行触发器，但是出现了变异表错误.SQL代码是这样的:

I'm trying to do a trigger but I get a mutating table error. The SQL code is something like this:

CREATE OR REPLACE TRIGGER CHK_Apartado_D
BEFORE INSERT OR UPDATE ON CONTRACTS
FOR EACH ROW
DECLARE 
errorvisualizacion EXCEPTION;
local_enddate DATE;
BEGIN
  SELECT enddate INTO local_enddate FROM CONTRACTS WHERE clientid=:new.clientid;
  IF local_enddate > SYSDATE OR local_enddate IS NULL
  THEN 
    UPDATE CONTRACTS SET enddate = SYSDATE - 1 WHERE clientid=:new.clientid;
  END IF;
END CHK_Apartado_B;
/

我得到的错误是:

Informe de error -
Error SQL: ORA-04091: table HR.CONTRACTS is mutating, trigger/function may not see it
ORA-06512: at "HR.CHK_APARTADO_D", line 5
ORA-04088: error during execution of trigger 'HR.CHK_APARTADO_D'
ORA-06512: at "HR.CHK_APARTADO_D", line 8
ORA-04088: error during execution of trigger 'HR.CHK_APARTADO_D'
04091. 00000 -  "table %s.%s is mutating, trigger/function may not see it"
*Cause:    A trigger (or a user defined plsql function that is referenced in
           this statement) attempted to look at (or modify) a table that was
           in the middle of being modified by the statement which fired it.
*Action:   Rewrite the trigger (or function) so it does not read that table.

当我插入新合同时，我必须检查该客户在实际日期中是否还有其他合同，如果他有，我必须将截止日期合同更新为昨天并让新INSERT.那么，我该怎么做才能防止表格发生变异?

When I INSERT a new contract I have to check if that client have an other contract in actual date, and if he have I must update de end date contracte to yesterday and let the new INSERT. So, how can I do to prevent the mutating table?

推荐答案

您的触发器在更新或插入CONTRACTS时触发，然后尝试更新CONTRACTS，从而触发该触发器....看到问题了吗?

Your trigger fires on update or insert of CONTRACTS and then tries to update CONTRACTS, which fires the trigger.... See the problem?

您需要计算结束日期，然后实际进行插入/更新.

You need to work out the enddate and then actually do the insert / update.

这篇关于Oracle SQL触发器上的突变表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！