brids - 1 year ago 63
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)
);
``````

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download