How to let a column have values reflected automatically, when value is inserted/updated/deleted to/from another column in the same table?

Question

I created 2 tables : INFORMATION AND FEED.

INFORMATION has 2 attributes : ID(Primary Key), TOT_AMOUNT.

FEED has 4 attributes : ID(Foreign key refer INFORMATION(ID)), S_AMOUNT, S_DATE, TOT_REM.

Now, I have to insert/update/delete values to/from TOT_REM, based on the insertion/removal/update of S_AMOUNT and TOT_AMOUNT.

The sample contents are :

INFORMATION Table
------------------
ID   |  TOT_AMOUNT
1    |    100
2    |    20
3    |    50
...

               FEED Table
----------------------------------------
ID   |   S_AMOUNT   |  S_DATE  | TOT_REM 
1    |     10       |10.10.2010|   90
1    |     10       |13.10.2010|   80
1    |     30       |17.10.2013|   50
1    |     10       |20.10.2016|   40
...

We need to automatically insert the value into TOT_REM attribute, based on the update/insert/delete operation performed on S_AMOUNT, with the help of TOT_AMOUNT & S_AMOUNT.

At any time, TOT_REM can't be less than 0. And, TOT_REM needs to be automatically inserted/removed/updated such that

TOT_REM for i(at a specific date) = (TOT_AMOUNT for ID=i) - 
                                    SUM(S_AMOUNT of all instances of ID=i,
                                    which is later than the S_DATE for ID=i); 

So, assuming if we delete the 2nd tuple(1,10,'13.10.2010',80), the reflected state of BR_FEED should be :

               FEED Table
----------------------------------------
ID   |   S_AMOUNT   |  S_DATE  | TOT_REM 
1    |     10       |10.10.2010|   90
1    |     30       |17.10.2013|   60
1    |     10       |20.10.2016|   50
...

I wrote a trigger, which fails showing

ORA-04091: table SSUMAN.FEED is mutating, trigger/function may not see it

The code for trigger is :

CREATE OR REPLACE TRIGGER BR_INSERT_TRB
AFTER DELETE OR INSERT OR UPDATE OF S_AMOUNT ON FEED
FOR EACH ROW
BEGIN

IF DELETING THEN 
UPDATE  FEED bf
SET     bf.TOT_REM = bf.S_AMOUNT + :OLD.S_AMOUNT;
END IF;

IF INSERTING THEN 
INSERT INTO FEED (TOT_REM) VALUES(
((SELECT TOT_AMOUNT FROM INFORMATION bi WHERE bi.ID=:NEW.ID) - 
(SELECT SUM(S_AMOUNT) FROM FEED bf where bf.ID=:NEW.ID) - 
:NEW.S_AMOUNT);
END IF;

IF UPDATING THEN 
UPDATE  FEED bf
SET     bf.TOT_REM = (SELECT TOT_AMOUNT FROM BR_INFORMATION bi WHERE bi.ID=bf.ID) - 
(SELECT SUM(S_AMOUNT) FROM FEED bf where bf.ID=:NEW.ID) - 
 :NEW.S_AMOUNT
WHERE   :NEW.ID IS NOT NULL;
END IF;   

END;

Questions :

1. Is this approach flawed? Can't I achieve what I want, with this way?[OPTIONAL]
2. Is there any scope of bringing view here? I am not able to think in that line! Probably, lack of experience...[OPTIONAL]
3. Any better approach, so that TOT_REM values can be automatically reflected?[COMPULSORY TO ANSWER]

Answer 1

I think it is better to create view. Look at this

Test data

create table feed(ID,S_AMOUNT,S_DATE) as  (
  SELECT 1,10, TO_DATE('10.10.2010','dd.mm.yyyy') FROM dual UNION all
  SELECT 1,10,TO_DATE('13.10.2010','dd.mm.yyyy') FROM dual UNION all
  SELECT 1,30,TO_DATE('17.10.2013','dd.mm.yyyy') FROM dual UNION all
  SELECT 2,10,TO_DATE('20.10.2016','dd.mm.yyyy') FROM dual)

create table INFORMATION (id, TOT_AMOUNT) as (
  SELECT 1,100 FROM DUAL UNION ALL
  SELECT 2,20 FROM DUAL UNION ALL
  SELECT 3,50 FROM DUAL)

Query

create or replace view result_feed as
  SELECT f.*,i.TOT_AMOUNT - NVL(SUM(S_AMOUNT) OVER(PARTITION BY f.ID ORDER BY f.S_DATE),0) AS tot_rem FROM FEED f, INFORMATION i
    WHERE f.ID = i.id
  ORDER BY f.ID, f.S_DATE;
  -- used NVL to prevent side-effect of null values


  SELECT * from RESULT_FEED;

Your approach with with trigger is not suitable in this situation. I think data is added seldom and query needed only in special cases. Of course there is some approaches to workaround mutating table(package variables,compound triggers, autonoumous transaction) but I think they only add perfomance problems to your database.