I would like update the balance of the Parent account(s) using the sum of child account(s). Can you please guide ?
Thanks for the test case with Live SQL.
SQL> drop table chao purge;
Table dropped.
SQL>
SQL> CREATE TABLE CHAO (
2 CHAO_ACC VARCHAR2(30),
3 CHAO_DESC VARCHAR2(100),
4 CHAO_CLASS VARCHAR2(30),
5 CHAO_PARENT_ACC VARCHAR2(30),
6 CHAO_BAL NUMBER(19,2)
7 )
8 /
Table created.
SQL>
SQL> SET DEFINE OFF;
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_BAL)
3 Values
4 ('1', 'Assets', 'A', 0);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('1.101', 'Cash', 'A', '1', 0);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('1.101.1', 'Bank A/C', 'A', '1.101', 200000);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('1.101.2', 'Current A/c OD', 'A', '1.101', 1000);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('1.101.3', 'Time Deposit', 'A', '1.101', 10000);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('1.101.3.1', 'Income From Time Deposit (Commission)', 'A', '1.101.3', 200);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('1.201', 'Investments', 'A', '1', 0);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('1.201.1', 'Quoted Investments', 'A', '1.201', 200000);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('1.202.2', 'Unquoted Investments', 'A', '1.201', 300);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('1.301', 'Receivables', 'A', '1', 0);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('1.301.1', 'Trade Receivable', 'A', '1.301', 3500);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('1.302.2', 'Other Receivable', 'A', '1.301', 0);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('1.401', 'Fee - Incomes', 'A', '1', 0);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('1.401.1', 'Subscription/Redemption Fees', 'A', '1.401', 20000);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('1.402.2', 'Management Fee', 'A', '1.401', 3200);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('1.403.3', 'Performance Fee', 'A', '1.401', 0);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('1.404.4', 'Admin Fee', 'A', '1.401', 0);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('1.501', 'Other Assets', 'A', '1', 0);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_BAL)
3 Values
4 ('2', 'Liabilities', 'L', 0);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('2.101', 'Trade Payables', 'L', '2', 4000);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('2.102', 'Bank Overdraft', 'L', '2', 0);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('2.103', 'Other Payables and Credit Balance', 'L', '2', 0);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('2.104', 'Loan', 'L', '2', 0);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('2.105', 'Redemption', 'L', '2', 2000);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('2.201', 'Fees - Expences', 'L', '2', 0);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('2.201.1', 'CMA Fees', 'L', '2.201', 2300);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('2.201.2', 'Auditing Fees', 'L', '2.201', 1500);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('2.201.3', 'Tadawul Fees', 'L', '2.201', 0);
1 row created.
SQL> Insert into CHAO
2 (CHAO_ACC, CHAO_DESC, CHAO_CLASS, CHAO_PARENT_ACC, CHAO_BAL)
3 Values
4 ('2.201.4', 'Other Fees', 'L', '2.201', 0);
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
SQL> alter table CHAO add sum_plus_children number(19,2);
Table altered.
SQL>
SQL> select chao_acc, chao_bal, chao_parent_acc, chao_bal, sum_plus_children
2 from CHAO;
CHAO_ACC CHAO_BAL CHAO_PARENT_ACC CHAO_BAL SUM_PLUS_CHILDREN
------------------------------ ---------- ------------------------------ ---------- -----------------
1 0 0
1.101 0 1 0
1.101.1 200000 1.101 200000
1.101.2 1000 1.101 1000
1.101.3 10000 1.101 10000
1.101.3.1 200 1.101.3 200
1.201 0 1 0
1.201.1 200000 1.201 200000
1.202.2 300 1.201 300
1.301 0 1 0
1.301.1 3500 1.301 3500
1.302.2 0 1.301 0
1.401 0 1 0
1.401.1 20000 1.401 20000
1.402.2 3200 1.401 3200
1.403.3 0 1.401 0
1.404.4 0 1.401 0
1.501 0 1 0
2 0 0
2.101 4000 2 4000
2.102 0 2 0
2.103 0 2 0
2.104 0 2 0
2.105 2000 2 2000
2.201 0 2 0
2.201.1 2300 2.201 2300
2.201.2 1500 2.201 1500
2.201.3 0 2.201 0
2.201.4 0 2.201 0
29 rows selected.
SQL>
SQL> --
SQL> -- Initialize the column
SQL> --
SQL> update chao c
2 set sum_plus_children =
3 ( select sum(chao_bal)
4 from chao
5 connect by prior chao_acc = chao_parent_acc
6 start with chao_acc = c.chao_acc
7 ) ;
29 rows updated.
SQL>
SQL>
SQL> select chao_acc, chao_bal, chao_parent_acc, chao_bal, sum_plus_children
2 from CHAO;
CHAO_ACC CHAO_BAL CHAO_PARENT_ACC CHAO_BAL SUM_PLUS_CHILDREN
------------------------------ ---------- ------------------------------ ---------- -----------------
1 0 0 438200
1.101 0 1 0 211200
1.101.1 200000 1.101 200000 200000
1.101.2 1000 1.101 1000 1000
1.101.3 10000 1.101 10000 10200
1.101.3.1 200 1.101.3 200 200
1.201 0 1 0 200300
1.201.1 200000 1.201 200000 200000
1.202.2 300 1.201 300 300
1.301 0 1 0 3500
1.301.1 3500 1.301 3500 3500
1.302.2 0 1.301 0 0
1.401 0 1 0 23200
1.401.1 20000 1.401 20000 20000
1.402.2 3200 1.401 3200 3200
1.403.3 0 1.401 0 0
1.404.4 0 1.401 0 0
1.501 0 1 0 0
2 0 0 9800
2.101 4000 2 4000 4000
2.102 0 2 0 0
2.103 0 2 0 0
2.104 0 2 0 0
2.105 2000 2 2000 2000
2.201 0 2 0 3800
2.201.1 2300 2.201 2300 2300
2.201.2 1500 2.201 1500 1500
2.201.3 0 2.201 0 0
2.201.4 0 2.201 0 0
29 rows selected.
SQL>
SQL> create or replace type chao_acct_list is table of varchar2(30);
2 /
Type created.
SQL>
SQL> create or replace
2 package chao_pkg is
3 trigger_level int := 1;
4 end;
5 /
Package created.
SQL>
SQL>
SQL> create or replace trigger TRG
2 for insert or update or delete on CHAO
3 compound trigger
4
5 type t_child_idx is table of pls_integer index by varchar2(30);
6 l_child_idx t_child_idx;
7
8 l_all_keys chao_acct_list := chao_acct_list();
9
10 l_idx varchar2(30);
11
12 -- before statement is
13 -- begin
14 -- l_child_idx.delete;
15 -- l_all_keys := chao_acct_list();
16 -- end before statement;
17
18 after each row is
19 begin
20 if chao_pkg.trigger_level = 1 then
21 if inserting then
22 dbms_output.put_line('---ins: '||:new.CHAO_ACC);
23 l_child_idx(:new.CHAO_ACC) := 1;
24 end if;
25
26 if updating then
27 dbms_output.put_line('---upd: '||:new.CHAO_ACC||'/'||:old.CHAO_ACC);
28 l_child_idx(:new.CHAO_ACC) := 1;
29 l_child_idx(:old.CHAO_ACC) := 1;
30 end if;
31
32 if deleting then
33 dbms_output.put_line('---del: '||:new.CHAO_PARENT_ACC);
34 l_child_idx(:old.CHAO_PARENT_ACC) := 1;
35 end if;
36 end if;
37 end after each row;
38
39 after statement is
40 begin
41 if chao_pkg.trigger_level = 1 then
42 chao_pkg.trigger_level := chao_pkg.trigger_level + 1;
43 lock table chao in exclusive mode nowait;
44
45 --
46 -- now we pick up every possible key that could be impacted, either
47 -- by direct update, or it is a parent of a row that had a direct update
48 --
49 l_idx := l_child_idx.first;
50 while l_idx is not null
51 loop
52 dbms_output.put_line('Directly affected key: '||l_idx);
53 for i in (select chao_acc
54 from chao
55 connect by chao_acc = prior chao_parent_acc
56 start with chao_acc = l_idx )
57 loop
58 dbms_output.put_line('- could affect key: '||i.chao_acc);
59 l_all_keys.extend;
60 l_all_keys(l_all_keys.count) := i.chao_acc;
61 end loop;
62
63 l_idx := l_child_idx.next(l_idx);
64 end loop;
65 --
66 -- Now each key identified, we run our standard update like we did
67 -- outside the trigger to update the totals
68 --
69 for i in ( select distinct column_value chao_acc
70 from table(l_all_keys)
71 )
72 loop
73 dbms_output.put_line('Resetting total for: '||i.chao_acc);
74 update chao c
75 set sum_plus_children =
76 ( select sum(chao_bal)
77 from chao
78 connect by prior chao_acc = chao_parent_acc
79 start with chao_acc = c.chao_acc
80 )
81 where chao_acc = i.chao_acc;
82 end loop;
83 chao_pkg.trigger_level := chao_pkg.trigger_level - 1;
84 end if;
85 exception
86 when others then
87 chao_pkg.trigger_level := 1;
88 raise;
89 end after statement;
90
91 end;
92 /
Trigger created.
SQL> sho err
No errors.
SQL>
SQL> select chao_acc, chao_bal, chao_parent_acc, chao_bal, sum_plus_children
2 from CHAO;
CHAO_ACC CHAO_BAL CHAO_PARENT_ACC CHAO_BAL SUM_PLUS_CHILDREN
------------------------------ ---------- ------------------------------ ---------- -----------------
1 0 0 438200
1.101 0 1 0 211200
1.101.1 200000 1.101 200000 200000
1.101.2 1000 1.101 1000 1000
1.101.3 10000 1.101 10000 10200
1.101.3.1 200 1.101.3 200 200
1.201 0 1 0 200300
1.201.1 200000 1.201 200000 200000
1.202.2 300 1.201 300 300
1.301 0 1 0 3500
1.301.1 3500 1.301 3500 3500
1.302.2 0 1.301 0 0
1.401 0 1 0 23200
1.401.1 20000 1.401 20000 20000
1.402.2 3200 1.401 3200 3200
1.403.3 0 1.401 0 0
1.404.4 0 1.401 0 0
1.501 0 1 0 0
2 0 0 9800
2.101 4000 2 4000 4000
2.102 0 2 0 0
2.103 0 2 0 0
2.104 0 2 0 0
2.105 2000 2 2000 2000
2.201 0 2 0 3800
2.201.1 2300 2.201 2300 2300
2.201.2 1500 2.201 1500 1500
2.201.3 0 2.201 0 0
2.201.4 0 2.201 0 0
29 rows selected.
SQL>
SQL> set serverout on
SQL> update chao
2 set chao_bal = 10500
3 where chao_acc = '1.101.3';
---upd: 1.101.3/1.101.3
Directly affected key: 1.101.3
- could affect key: 1.101.3
- could affect key: 1.101
- could affect key: 1
Resetting total for: 1
Resetting total for: 1.101.3
Resetting total for: 1.101
1 row updated.
SQL>
SQL> select chao_acc, chao_bal, chao_parent_acc, chao_bal, sum_plus_children
2 from CHAO;
CHAO_ACC CHAO_BAL CHAO_PARENT_ACC CHAO_BAL SUM_PLUS_CHILDREN
------------------------------ ---------- ------------------------------ ---------- -----------------
1 0 0 438700
1.101 0 1 0 211700
1.101.1 200000 1.101 200000 200000
1.101.2 1000 1.101 1000 1000
1.101.3 10500 1.101 10500 10700
1.101.3.1 200 1.101.3 200 200
1.201 0 1 0 200300
1.201.1 200000 1.201 200000 200000
1.202.2 300 1.201 300 300
1.301 0 1 0 3500
1.301.1 3500 1.301 3500 3500
1.302.2 0 1.301 0 0
1.401 0 1 0 23200
1.401.1 20000 1.401 20000 20000
1.402.2 3200 1.401 3200 3200
1.403.3 0 1.401 0 0
1.404.4 0 1.401 0 0
1.501 0 1 0 0
2 0 0 9800
2.101 4000 2 4000 4000
2.102 0 2 0 0
2.103 0 2 0 0
2.104 0 2 0 0
2.105 2000 2 2000 2000
2.201 0 2 0 3800
2.201.1 2300 2.201 2300 2300
2.201.2 1500 2.201 1500 1500
2.201.3 0 2.201 0 0
2.201.4 0 2.201 0 0
29 rows selected.
SQL>