Home > Mobile >  Oracle, ORA-04091 mutation issues, updating a table without firing triggers within a trigger
Oracle, ORA-04091 mutation issues, updating a table without firing triggers within a trigger

Time：10-13

This is the situation of my problem:

Our company is selling a BI Application with Data Entry, it basically runs an Update for every row where the values changed.

  1. I am trying to build a function for our users to create budgets.
  2. In my Table, I have 12 Fields for Account Postings (Debit - Credit of that month) and 12 for Account Balances for each months.
  3. I want that if my user does Data Entry on the Postings, the Balances for months gets updated accordingly and if my user does Data Entry on the Balances, the Postings gets adjusted accordingly
  4. Data Entry cannot be done on Postings and Balances at the same time (2 separate worksheets)

I did it in SQL Server with a trigger to check if the Postings was changed, re-calculate the Balances and vice versa.

I tried to do the same in Oracle (we have to support both and I am no Oracle expert) but I keep getting the Mutation error, I guess it's because it's stuck in a loop where: Posting -> update Balance -> update Posting -> etc.

I looked around and tried different solutions like disabling the trigger before update within the trigger and re-enable it after with anonymous transaction but the update just won't resolve.

I tried to add a flag that gets updated to 1 within the trigger and fire the trigger only when the new value of it is 0 (with another trigger that resets it to 0 after) but it just doesn't seem to work.

I tried to use a temporary table to record which rows changed to see if it's a Posting change or Balance change then run the update without a FOR EACH ROW but it's still causing an issue.

Does anyone have any ideas? Code is like this:

    CREATE OR REPLACE TRIGGER "QA_BUD_V8"."UPDATE_BUDGET_VALUES" 
    AFTER UPDATE OF
    B_POSTING01,B_POSTING02,B_POSTING03,B_POSTING04,B_POSTING05,B_POSTING06,B_POSTING07,B_POSTING08,B_POSTING09,B_POSTING10,B_POSTING11,B_POSTING12
    ,B_OPENINGBALANCE
    ,B_ENDINGBALANCE01,B_ENDINGBALANCE02,B_ENDINGBALANCE03,B_ENDINGBALANCE04,B_ENDINGBALANCE05,B_ENDINGBALANCE06,B_ENDINGBALANCE07,B_ENDINGBALANCE08,B_ENDINGBALANCE09,B_ENDINGBALANCE10,B_ENDINGBALANCE11,B_ENDINGBALANCE12
    ON "QA_BUD_V8".UDM_BUDGET_INWORK 
    FOR EACH ROW
    BEGIN
    IF (:OLD.B_OPENINGBALANCE <> :NEW.B_OPENINGBALANCE
    OR :OLD.B_POSTING01 <> :NEW.B_POSTING01
    OR :OLD.B_POSTING02 <> :NEW.B_POSTING02
    OR :OLD.B_POSTING03 <> :NEW.B_POSTING03
    OR :OLD.B_POSTING04 <> :NEW.B_POSTING04
    OR :OLD.B_POSTING05 <> :NEW.B_POSTING05
    OR :OLD.B_POSTING06 <> :NEW.B_POSTING06
    OR :OLD.B_POSTING07 <> :NEW.B_POSTING07
    OR :OLD.B_POSTING08 <> :NEW.B_POSTING08
    OR :OLD.B_POSTING09 <> :NEW.B_POSTING09
    OR :OLD.B_POSTING10 <> :NEW.B_POSTING10
    OR :OLD.B_POSTING11 <> :NEW.B_POSTING11
    OR :OLD.B_POSTING12 <> :NEW.B_POSTING12) THEN

        [UPDATE Balances with Postings]
    
    ELSIF (:OLD.B_ENDINGBALANCE01 <> :NEW.B_ENDINGBALANCE01
    OR :OLD.B_ENDINGBALANCE02 <> :NEW.B_ENDINGBALANCE02
    OR :OLD.B_ENDINGBALANCE03 <> :NEW.B_ENDINGBALANCE03
    OR :OLD.B_ENDINGBALANCE04 <> :NEW.B_ENDINGBALANCE04
    OR :OLD.B_ENDINGBALANCE05 <> :NEW.B_ENDINGBALANCE05
    OR :OLD.B_ENDINGBALANCE06 <> :NEW.B_ENDINGBALANCE06
    OR :OLD.B_ENDINGBALANCE07 <> :NEW.B_ENDINGBALANCE07
    OR :OLD.B_ENDINGBALANCE08 <> :NEW.B_ENDINGBALANCE08
    OR :OLD.B_ENDINGBALANCE09 <> :NEW.B_ENDINGBALANCE09
    OR :OLD.B_ENDINGBALANCE10 <> :NEW.B_ENDINGBALANCE10
    OR :OLD.B_ENDINGBALANCE11 <> :NEW.B_ENDINGBALANCE11
    OR :OLD.B_ENDINGBALANCE12 <> :NEW.B_ENDINGBALANCE12) THEN

        [UPDATE Postings with Balances]
    END IF;
    END;

Is there a way within the trigger to update without firing a trigger or to get out of the loop that causes mutation? Unfortunately, I only have control on some of the SQL portion of the code. Rather than have a module within the Application explicitly for budgeting, I am trying to make use of the Data Entry Function in the Application to do budgeting so there are limitations of what I can do.

CodePudding user response：

Ok, it seems like you are having an issue with my comment, so here is a code example of how to use a package variable to solve your issue:

CREATE OR REPLACE PACKAGE pk_trigger_chk IS
  suppress BOOLEAN := FALSE;
END pk_trigger_chk;
/

That package creates the variable you would need to put into your trigger definitions.

CREATE OR REPLACE TRIGGER trg_1 AFTER UPDATE ON a FOR EACH ROW WHEN (new.val<>old.val)
BEGIN
  IF NOT(pk_trigger_chk.suppress) THEN
    pk_trigger_chk.suppress := TRUE;
    
    UPDATE b
    SET b.val = :new.val
    WHERE b.id = :new.id;
    
    pk_trigger_chk.suppress := FALSE;
 END IF;
EXCEPTION WHEN OTHERS THEN
  pk_trigger_chk.suppress := FALSE;
END trg_1;
/

That is how you update your trigger to use the package variable.

Here is a DBFiddle showing it in action. The DBFiddle shows both the before and after of updating the trigger with the package variable. As well as the error you would get without using it. (LINK)

CodePudding user response：

I found my own answer.

In the end, I was too obsessed with AFTER UPDATE. I changed it to BEFORE UPDATE, did an if to check if it was the postings or balances that changed, then I assigned a new value to the other set of measures before the update and my issue was all solved... I wasted 2 days on this lol

Here's the code if interested

    CREATE OR REPLACE TRIGGER "QA_BUD_V8"."UPDATE_BUDGET_VALUES" 
    BEFORE UPDATE ON "QA_BUD_V8".UDM_BUDGET_INWORK
    FOR EACH ROW
    DECLARE
        v_CLOSE_TO_ACCOUNT varchar2(100);
    BEGIN
        SELECT count(CASE WHEN CLOSETOACCOUNT IS NOT NULL AND CLOSETOACCOUNT <> ' ' THEN 1 END) into v_CLOSE_TO_ACCOUNT FROM "QA_BUD_V8".UDM_ACCOUNTS WHERE COA = :OLD.COA AND ACCOUNTNUM = :OLD.ACCOUNTID;
    
        IF (:OLD.B_OPENINGBALANCE <> :NEW.B_OPENINGBALANCE
        OR :OLD.B_POSTING01 <> :NEW.B_POSTING01
        OR :OLD.B_POSTING02 <> :NEW.B_POSTING02
        OR :OLD.B_POSTING03 <> :NEW.B_POSTING03
        OR :OLD.B_POSTING04 <> :NEW.B_POSTING04
        OR :OLD.B_POSTING05 <> :NEW.B_POSTING05
        OR :OLD.B_POSTING06 <> :NEW.B_POSTING06
        OR :OLD.B_POSTING07 <> :NEW.B_POSTING07
        OR :OLD.B_POSTING08 <> :NEW.B_POSTING08
        OR :OLD.B_POSTING09 <> :NEW.B_POSTING09
        OR :OLD.B_POSTING10 <> :NEW.B_POSTING10
        OR :OLD.B_POSTING11 <> :NEW.B_POSTING11
        OR :OLD.B_POSTING12 <> :NEW.B_POSTING12) THEN
            
        
        :NEW.B_ENDINGBALANCE01 := :NEW.B_OPENINGBALANCE   :NEW.B_POSTING01;
        :NEW.B_ENDINGBALANCE02 := :NEW.B_OPENINGBALANCE   :NEW.B_POSTING01   :NEW.B_POSTING02
        - CASE
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =12 THEN :NEW.B_ENDINGBALANCE01
            ELSE 0
        END;
        :NEW.B_ENDINGBALANCE03 := :NEW.B_OPENINGBALANCE   :NEW.B_POSTING01   :NEW.B_POSTING02   :NEW.B_POSTING03
        - CASE
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =11 THEN :NEW.B_ENDINGBALANCE02
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =12 THEN :NEW.B_ENDINGBALANCE01
            ELSE 0
        END;
        :NEW.B_ENDINGBALANCE04 := :NEW.B_OPENINGBALANCE   :NEW.B_POSTING01   :NEW.B_POSTING02   :NEW.B_POSTING03  :NEW.B_POSTING04
        - CASE
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =10 THEN :NEW.B_ENDINGBALANCE03
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =11 THEN :NEW.B_ENDINGBALANCE02
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =12 THEN :NEW.B_ENDINGBALANCE01
            ELSE 0
        END;
        :NEW.B_ENDINGBALANCE05 := :NEW.B_OPENINGBALANCE   :NEW.B_POSTING01   :NEW.B_POSTING02   :NEW.B_POSTING03  :NEW.B_POSTING04   :NEW.B_POSTING05
        - CASE
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 9 THEN :NEW.B_ENDINGBALANCE04
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =10 THEN :NEW.B_ENDINGBALANCE03
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =11 THEN :NEW.B_ENDINGBALANCE02
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =12 THEN :NEW.B_ENDINGBALANCE01
            ELSE 0
        END;
        :NEW.B_ENDINGBALANCE06 := :NEW.B_OPENINGBALANCE   :NEW.B_POSTING01   :NEW.B_POSTING02   :NEW.B_POSTING03  :NEW.B_POSTING04   :NEW.B_POSTING05   :NEW.B_POSTING06
        - CASE
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 8 THEN :NEW.B_ENDINGBALANCE05
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 9 THEN :NEW.B_ENDINGBALANCE04
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =10 THEN :NEW.B_ENDINGBALANCE03
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =11 THEN :NEW.B_ENDINGBALANCE02
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =12 THEN :NEW.B_ENDINGBALANCE01
            ELSE 0
        END;
        :NEW.B_ENDINGBALANCE07 := :NEW.B_OPENINGBALANCE   :NEW.B_POSTING01   :NEW.B_POSTING02   :NEW.B_POSTING03  :NEW.B_POSTING04   :NEW.B_POSTING05   :NEW.B_POSTING06   :NEW.B_POSTING07
        - CASE
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 7 THEN :NEW.B_ENDINGBALANCE06
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 8 THEN :NEW.B_ENDINGBALANCE05
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 9 THEN :NEW.B_ENDINGBALANCE04
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =10 THEN :NEW.B_ENDINGBALANCE03
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =11 THEN :NEW.B_ENDINGBALANCE02
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =12 THEN :NEW.B_ENDINGBALANCE01
            ELSE 0
        END;
        :NEW.B_ENDINGBALANCE08 := :NEW.B_OPENINGBALANCE   :NEW.B_POSTING01   :NEW.B_POSTING02   :NEW.B_POSTING03  :NEW.B_POSTING04   :NEW.B_POSTING05   :NEW.B_POSTING06   :NEW.B_POSTING07   :NEW.B_POSTING08
        - CASE
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 6 THEN :NEW.B_ENDINGBALANCE07
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 7 THEN :NEW.B_ENDINGBALANCE06
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 8 THEN :NEW.B_ENDINGBALANCE05
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 9 THEN :NEW.B_ENDINGBALANCE04
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =10 THEN :NEW.B_ENDINGBALANCE03
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =11 THEN :NEW.B_ENDINGBALANCE02
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =12 THEN :NEW.B_ENDINGBALANCE01
            ELSE 0
        END;
        :NEW.B_ENDINGBALANCE09 := :NEW.B_OPENINGBALANCE   :NEW.B_POSTING01   :NEW.B_POSTING02   :NEW.B_POSTING03  :NEW.B_POSTING04   :NEW.B_POSTING05   :NEW.B_POSTING06   :NEW.B_POSTING07   :NEW.B_POSTING08   :NEW.B_POSTING09
        - CASE
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 5 THEN :NEW.B_ENDINGBALANCE08
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 6 THEN :NEW.B_ENDINGBALANCE07
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 7 THEN :NEW.B_ENDINGBALANCE06
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 8 THEN :NEW.B_ENDINGBALANCE05
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 9 THEN :NEW.B_ENDINGBALANCE04
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =10 THEN :NEW.B_ENDINGBALANCE03
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =11 THEN :NEW.B_ENDINGBALANCE02
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =12 THEN :NEW.B_ENDINGBALANCE01
            ELSE 0
        END;
        :NEW.B_ENDINGBALANCE10 := :NEW.B_OPENINGBALANCE   :NEW.B_POSTING01   :NEW.B_POSTING02   :NEW.B_POSTING03  :NEW.B_POSTING04   :NEW.B_POSTING05   :NEW.B_POSTING06   :NEW.B_POSTING07   :NEW.B_POSTING08   :NEW.B_POSTING09   :NEW.B_POSTING10
        - CASE
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 4 THEN :NEW.B_ENDINGBALANCE09
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 5 THEN :NEW.B_ENDINGBALANCE08
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 6 THEN :NEW.B_ENDINGBALANCE07
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 7 THEN :NEW.B_ENDINGBALANCE06
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 8 THEN :NEW.B_ENDINGBALANCE05
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 9 THEN :NEW.B_ENDINGBALANCE04
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =10 THEN :NEW.B_ENDINGBALANCE03
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =11 THEN :NEW.B_ENDINGBALANCE02
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =12 THEN :NEW.B_ENDINGBALANCE01
            ELSE 0
        END;
        :NEW.B_ENDINGBALANCE11 := :NEW.B_OPENINGBALANCE   :NEW.B_POSTING01   :NEW.B_POSTING02   :NEW.B_POSTING03  :NEW.B_POSTING04   :NEW.B_POSTING05   :NEW.B_POSTING06   :NEW.B_POSTING07   :NEW.B_POSTING08   :NEW.B_POSTING09   :NEW.B_POSTING10   :NEW.B_POSTING11
        - CASE
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 3 THEN :NEW.B_ENDINGBALANCE10
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 4 THEN :NEW.B_ENDINGBALANCE09
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 5 THEN :NEW.B_ENDINGBALANCE08
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 6 THEN :NEW.B_ENDINGBALANCE07
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 7 THEN :NEW.B_ENDINGBALANCE06
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 8 THEN :NEW.B_ENDINGBALANCE05
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 9 THEN :NEW.B_ENDINGBALANCE04
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =10 THEN :NEW.B_ENDINGBALANCE03
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =11 THEN :NEW.B_ENDINGBALANCE02
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =12 THEN :NEW.B_ENDINGBALANCE01
            ELSE 0
        END;
        :NEW.B_ENDINGBALANCE12 := :NEW.B_OPENINGBALANCE   :NEW.B_POSTING01   :NEW.B_POSTING02   :NEW.B_POSTING03  :NEW.B_POSTING04   :NEW.B_POSTING05   :NEW.B_POSTING06   :NEW.B_POSTING07   :NEW.B_POSTING08   :NEW.B_POSTING09   :NEW.B_POSTING10   :NEW.B_POSTING11   :NEW.B_POSTING12
        - CASE
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 2 THEN :NEW.B_ENDINGBALANCE11
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 3 THEN :NEW.B_ENDINGBALANCE10
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 4 THEN :NEW.B_ENDINGBALANCE09
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 5 THEN :NEW.B_ENDINGBALANCE08
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 6 THEN :NEW.B_ENDINGBALANCE07
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 7 THEN :NEW.B_ENDINGBALANCE06
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 8 THEN :NEW.B_ENDINGBALANCE05
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 9 THEN :NEW.B_ENDINGBALANCE04
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =10 THEN :NEW.B_ENDINGBALANCE03
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =11 THEN :NEW.B_ENDINGBALANCE02
            WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =12 THEN :NEW.B_ENDINGBALANCE01
            ELSE 0
        END;
    
        
        ELSIF (:OLD.B_ENDINGBALANCE01 <> :NEW.B_ENDINGBALANCE01
        OR :OLD.B_ENDINGBALANCE02 <> :NEW.B_ENDINGBALANCE02
        OR :OLD.B_ENDINGBALANCE03 <> :NEW.B_ENDINGBALANCE03
        OR :OLD.B_ENDINGBALANCE04 <> :NEW.B_ENDINGBALANCE04
        OR :OLD.B_ENDINGBALANCE05 <> :NEW.B_ENDINGBALANCE05
        OR :OLD.B_ENDINGBALANCE06 <> :NEW.B_ENDINGBALANCE06
        OR :OLD.B_ENDINGBALANCE07 <> :NEW.B_ENDINGBALANCE07
        OR :OLD.B_ENDINGBALANCE08 <> :NEW.B_ENDINGBALANCE08
        OR :OLD.B_ENDINGBALANCE09 <> :NEW.B_ENDINGBALANCE09
        OR :OLD.B_ENDINGBALANCE10 <> :NEW.B_ENDINGBALANCE10
        OR :OLD.B_ENDINGBALANCE11 <> :NEW.B_ENDINGBALANCE11
        OR :OLD.B_ENDINGBALANCE12 <> :NEW.B_ENDINGBALANCE12) THEN
    
        :NEW.B_POSTING01 := :NEW.B_ENDINGBALANCE01 - :NEW.B_OPENINGBALANCE;
        :NEW.B_POSTING02 := :NEW.B_ENDINGBALANCE02 - CASE WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 12 THEN 0 ELSE :NEW.B_ENDINGBALANCE01 END;
        :NEW.B_POSTING03 := :NEW.B_ENDINGBALANCE03 - CASE WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 11 THEN 0 ELSE :NEW.B_ENDINGBALANCE02 END;
        :NEW.B_POSTING04 := :NEW.B_ENDINGBALANCE04 - CASE WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD = 10 THEN 0 ELSE :NEW.B_ENDINGBALANCE03 END;
        :NEW.B_POSTING05 := :NEW.B_ENDINGBALANCE05 - CASE WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =  9 THEN 0 ELSE :NEW.B_ENDINGBALANCE04 END;
        :NEW.B_POSTING06 := :NEW.B_ENDINGBALANCE06 - CASE WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =  8 THEN 0 ELSE :NEW.B_ENDINGBALANCE05 END;
        :NEW.B_POSTING07 := :NEW.B_ENDINGBALANCE07 - CASE WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =  7 THEN 0 ELSE :NEW.B_ENDINGBALANCE06 END;
        :NEW.B_POSTING08 := :NEW.B_ENDINGBALANCE08 - CASE WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =  6 THEN 0 ELSE :NEW.B_ENDINGBALANCE07 END;
        :NEW.B_POSTING09 := :NEW.B_ENDINGBALANCE09 - CASE WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =  5 THEN 0 ELSE :NEW.B_ENDINGBALANCE08 END;
        :NEW.B_POSTING10 := :NEW.B_ENDINGBALANCE10 - CASE WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =  4 THEN 0 ELSE :NEW.B_ENDINGBALANCE09 END;
        :NEW.B_POSTING11 := :NEW.B_ENDINGBALANCE11 - CASE WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =  3 THEN 0 ELSE :NEW.B_ENDINGBALANCE10 END;
        :NEW.B_POSTING12 := :NEW.B_ENDINGBALANCE12 - CASE WHEN v_CLOSE_TO_ACCOUNT <> 0 AND :NEW.START_PERIOD =  2 THEN 0 ELSE :NEW.B_ENDINGBALANCE11 END;
        
        END IF;
    END;

Page link：https//www.codepudding.com/Mobile/149906.html

Prev:How to add current hour and minutes to inserted date
Next:Update column values by removing values after decimal points and comma in oracle
  • Related

About Us:  Contact Us      Terms of Service       Privacy Policy

Copyright © 2010-2023，Powered By CodePudding