每个记录ORACLE计算后的更新

Question

SELECT   CIF_ID, 
         SUM (IN_VERIFIED_DEBT + IN_FAC_WITH_OTHER + IN_FAC_WITH_BANK)
from    LOS_CIF_INDV
WHERE STATUS= 'ACTIVE' 
GROUP By CIF_ID;

在用户将客户端操作为update后，我希望再次更新总计列，插入，但会出现错误。

ORA-04098:触发器'RLOS138.UPDATE_IN_TOTAL_COMMIT‘无效，重新验证失败

CREATE OR REPLACE TRIGGER UPDATE_IN_TOTAL_COMMIT 
AFTER UPDATE ON 
LOS_CIF_INDV 
FOR EACH ROW
DECLARE 
  inactive_id number;
BEGIN
  inactive_id:= 
   :new.IN_VERIFIED_DEBT + :new.IN_FAC_WITH_OTHER + :new.IN_FAC_WITH_BANK;
  UPDAte LOS_CIF_INDV 
  SET IN_TOTAL_COMMIT = inactive_id
  WHERE CIF_ID = :NEW.CIF_ID;
END ;
/

我又试了一次

CREATE OR REPLACE TRIGGER RLOS138.UPDATE_IN_TOTAL_COMMIT 
AFTER UPDATE ON RLOS138.LOS_CIF_INDV 
FOR EACH ROW
DECLARE 
  inactive_id number;
BEGIN
  SELECT   SUM (IN_VERIFIED_DEBT+IN_FAC_WITH_OTHER+IN_FAC_WITH_BANK) 
  into inactive_id
  from    LOS_CIF_INDV
  WHERE STATUS= 'ACTIVE' 
  and CIF_ID=:NEw.CIF_ID;

  update  LOS_CIF_INDV
  set IN_TOTAL_COMMIT = inactive_id
  where CIF_ID = :NEW.CIF_ID;
END ;
/

Answer 1

是的，CIF_ID是主键

在这种情况下，此触发器具有所需的逻辑：

CREATE OR REPLACE TRIGGER RLOS138.UPDATE_IN_TOTAL_COMMIT 
BEFORE UPDATE ON RLOS138.LOS_CIF_INDV 
FOR EACH ROW
BEGIN

  if :new.status = 'ACTIVE' 
  then
    :new.IN_TOTAL_COMMIT := :new.IN_VERIFIED_DEBT + :new.IN_FAC_WITH_OTHER + :new.IN_FAC_WITH_BANK;
  end if;

END ;
/

我包含了对status的检查，因为您在聚合查询中使用了它，即使在触发器的第一个版本中省略了它。我还没有包括其他分支机构，但您可能希望增加一个分支机构。此外，我还假设加法中的三列肯定不是空的；如果不是这样的话，您需要处理这个问题。

我把a working demo on db<>fiddle放了。这包括触发器的一个版本，该触发器在插入和更新时触发，并处理空值.

CREATE OR REPLACE TRIGGER UPDATE_IN_TOTAL_COMMIT 
-- handle INSERT as well as UPDATE
BEFORE INSERT OR UPDATE ON LOS_CIF_INDV 
FOR EACH ROW
BEGIN

  if :new.status = 'ACTIVE' 
  then
    -- handle any of these columns being null
    :new.IN_TOTAL_COMMIT := nvl(:new.IN_VERIFIED_DEBT,0)
                          + nvl(:new.IN_FAC_WITH_OTHER,0) 
                          + nvl(:new.IN_FAC_WITH_BANK,0);
  end if;

END ;
/

为什么不先跟我解释一下

因为Oracle用这种方式编写了触发器:每一行触发器之后使用记录的最终版本，即将写入数据库的状态。因此，如果我们想要更改任何值，我们需要在每一行触发器之前使用一个。甲骨文用你得到的错误来强制执行，ORA-04084: cannot change NEW values for this trigger type。

提醒一下: ORA-04098告诉您触发代码中存在编译错误。如果您没有使用IDE来告诉您这些错误是什么，您可以通过以下查询找到它们：

select * from all_errors 
where owner = 'RLOS138' 
and name = 'UPDATE_IN_TOTAL_COMMIT' ;

(不确定是否以RLOS138的形式连接--如果是，则查询USER_ERRORS。)

Answer 2

如果我正确理解，您希望更新所有具有CIF_ID作为IN_TOTAL_COMMIT列中具有相同值的更新记录的记录。

这不是个好主意。如果您有一些派生列，则应该使用视图，而不是使用触发器更新每个insert/update的值。

如果确实希望更新列，则必须使用行级触发器、语句触发器和包变量的组合。(在SO中搜索可变表错误)

但据我所知，最好的解决办法是使用视图，如下所示：

CREATE OR REPLACE VIEW LOS_CIF_INDV_VW AS
    SELECT L.*,
           COALESCE(
               SUM(
                   CASE
                       WHEN STATUS = 'ACTIVE' THEN
                           IN_VERIFIED_DEBT + IN_FAC_WITH_OTHER + IN_FAC_WITH_BANK
                   END
               ) OVER(
                   PARTITION BY L.CIF_ID
               ),
               0
           ) AS IN_TOTAL_COMMIT
      FROM LOS_CIF_INDV L;