can we insert values into table using cursor in plsql oracle

Question

I want to insert sum of credit and debit of daily transaction from one table to another table when I run sql its give multi row function but when I insert into another table in plsql block it giving error

CREATE TABLE TRANSACTION_DAILY(
T_DATE  DATE,
CREDIT  NUMBER,
DEBIT   NUMBER
);

CREATE TABLE TRANSACTION_DAILY_total(
T_DATE  DATE,
total_CREDIT  NUMBER,
total_DEBIT   NUMBER
);

CREATE OR REPLACE trigger PRO_DAILY 
after insert on transaction_daily
declare
    CREDIT TRANSACTION_DAILY.credit%type;
    DEBIT  TRANSACTION_DAILY.debit%type;
    n_date TRANSACTION_DAILY.t_date%type;

    cursor c_daily
    is
        SELECT SUM( CREDIT ), SUM(DEBIT)
        FROM TRANSACTION_DAILY
        group by t_date;
begin
    open c_daily;

    fetch c_daily bull collect into  credit,debit,n_date;

    INSERT INTO TRANSACTION_DAILY_TOTAL (T_DATE,TOTAL_CREDIT,TOTAL_DEBIT) VALUES 
    (N_DATE,CREDIT,DEBIT);

    close c_daily;
END;

Answer 1

You don't need cursor. Actually, you don't need (nor want) a trigger at all. Here's why.

When fixed, trigger looks like this:

SQL> CREATE OR REPLACE TRIGGER pro_daily AFTER
  2      INSERT ON transaction_daily
  3  BEGIN
  4      INSERT INTO transaction_daily_total (
  5          t_date,
  6          total_credit,
  7          total_debit
  8      )
  9          SELECT
 10              t_date,
 11              SUM(credit),
 12              SUM(debit)
 13          FROM
 14              transaction_daily
 15          GROUP BY
 16              t_date;
 17  END;
 18  /

Trigger created.

SQL>

Does it work? Sure:

SQL> insert into transaction_daily (t_date, credit, debit)
  2    select date '2021-12-10', 100, 50 from dual union all
  3    select date '2021-12-10', 200, 75 from dual union all
  4    select date '2021-10-14', 500, 20 from dual;

3 rows created.

SQL> select * From transaction_daily_total;

T_DATE     TOTAL_CREDIT TOTAL_DEBIT
---------- ------------ -----------
2021-12-10          300         125
2021-10-14          500          20

SQL>

As expected, transaction_daily_total contains totals for those two dates. But, what if we insert just one another row?

SQL> insert into transaction_daily(t_date, credit, debit)
  2    values (date '2021-12-01', 1, 1);

1 row created.

SQL> select * From transaction_daily_total;

T_DATE     TOTAL_CREDIT TOTAL_DEBIT
---------- ------------ -----------
2021-12-10          300         125
2021-10-14          500          20
2021-12-10          300         125
2021-10-14          500          20
2021-12-01            1           1

SQL>

Whooops! Duplicates! Are you sure you really want that to happen?

---

What should you do? As I said - abandon the trigger & the "total" table idea and switch to a view:

SQL> create or replace view v_transaction_daily_total as
  2    select t_date, sum(credit) credit, sum(debit) debit
  3    from transaction_daily
  4    group by t_date;

View created.

SQL> select * From v_transaction_daily_total;

T_DATE         CREDIT      DEBIT
---------- ---------- ----------
2021-12-10        300        125
2021-10-14        500         20
2021-12-01          1          1

SQL>

Insert another row? No problem for the view:

SQL> insert into transaction_daily(t_date, credit, debit)
  2    values (date '2021-12-02', 50, 50);

1 row created.

SQL> select * From v_transaction_daily_total;

T_DATE         CREDIT      DEBIT
---------- ---------- ----------
2021-12-10        300        125
2021-10-14        500         20
2021-12-02         50         50
2021-12-01          1          1

SQL>