brids brids - 4 months ago 8
SQL Question

Calculate column value and insert another table

I have create two tables

deposit
and
final_balance
;I want to sum
deposit_amount
in
deposit
table and insert the result into
final_balance
table.
Can I can use a trigger?

CREATE TABLE deposit(cust_id VARCHAR2(10),loan_id VARCHAR2(10),deposit_type VARCHAR2(10),deposit_amount NUMBER(20));
INSERT INTO deposit values ('001CUS1','001LOAN1','Fixed',3500);
INSERT INTO deposit values ('001CUS2','001LOAN2','Fixed',3900);
INSERT INTO deposit values ('001CUS3','001LOAN3','Fixed',7000);
INSERT INTO deposit values ('001CUS4','001LOAN4','Fixed',28000);
INSERT INTO deposit values ('001CUS5','001LOAN5','Fixed',15200);
INSERT INTO deposit values ('001CUS6','001LOAN6','Fixed',2420);
INSERT INTO deposit values ('001CUS7','001LOAN7','Primary',2420);
INSERT INTO deposit values ('002CUS1','002LOAN1','Primary',27000);
INSERT INTO deposit values ('002CUS2','002LOAN2','Primary',20200);
INSERT INTO deposit values ('002CUS3','002LOAN3','Primary',6900);
CREATE TABLE final_balance(
total_deposit NUMBER(20)
);

Answer

If you need a one-shot evaluation, you can use:

SQL> insert into final_balance
  2    select sum(deposit_amount)
  3    from deposit;

1 row created.

If you need a self-updating result, a view would be a better solution:

SQL> CREATE VIEW v_final_balance(total_deposit) as
  2  select sum(deposit_amount)
  3    from deposit;

View created.

In the beginning, they both show the same result:

SQL> select * from final_balance;

TOTAL_DEPOSIT
-------------
       116540

SQL> select * from v_final_balance;

TOTAL_DEPOSIT
-------------
       116540

After updating data in deposit table, the view will show the updated total amount, with no need for triggers:

SQL> INSERT INTO deposit
  2       VALUES (
  3               'XX',
  4               'YY',
  5               'ZZ',
  6               1000000000
  7              );

1 row created.

SQL> select * from final_balance;

TOTAL_DEPOSIT
-------------
       116540

SQL> select * from v_final_balance;

TOTAL_DEPOSIT
-------------
   1000116540

You could even use a trigger-based approach, but it would need triggers for insert, update, delete, thus resulting in a more complex solution. If you have no specific needs for triggers, I believe the view is the best solution.