Update after calculate for each record 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;

I want to update the total column again after the user manipulates the client as update, insert but it gives an error

ORA-04098: trigger 'RLOS138.UPDATE_IN_TOTAL_COMMIT' is invalid and failed re-validation

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 ;
/

I have tried this again

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

yes [CIF_ID] is primary key

In which case this trigger has the logic you need:

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 ;
/

I have included the check on status because you used it in your aggregation queries, even though you omitted from the first version of the trigger. I haven't included an ELSE branch, but you may wish to add one. Also, I have assumed that the three columns in the addition are guaranteed to be not null; if that's not the case you'll need to handle that.

I have put a working demo on db<>fiddle. This includes a version of the trigger which fires on inserts as well as updates, and handles null values too....

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 ;
/

---

Why not after you could explain it to me

Because Oracle have written triggers that way: the AFTER EACH ROW trigger uses the finalised version of the record, the state which will be written to the database. Consequently, if we want to change any values we need to use a BEFORE EACH ROW trigger. Oracle enforces this with the error you got, ORA-04084: cannot change NEW values for this trigger type.

---

Just a reminder: ORA-04098 is telling you there are compilation errors in your trigger code. If you're not using an IDE which tells you what these errors are you can find them with this query:

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

(Not sure if you're connecting as RLOS138 - if you are, query USER_ERRORS instead.)

Answer 2

If I understood correctly, You want to update all the records having CIF_ID as an updated record with the same value in the IN_TOTAL_COMMIT column.

This is not a good idea. If you have some derived column then you should use the views instead of updating its value for every insert/update using the trigger.

If you really want to update the column then you must use the combination of Row level trigger, Statement trigger, and package variables. (Search for mutating table error in the SO)

But according to me, the best solution is to use the view, something like follows:

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;