create or replace 
PROCEDURE update_customer_totalamt
AS
v_custid NUMBER;
v_amount NUMBER;

BEGIN
    SELECT AMOUNT into v_amount 
    FROM invoice
    WHERE invoiceid = (SELECT MAX(invoiceid) FROM invoice);
    
    SELECT CUSTID into v_custid
    FROM invoice
    WHERE invoiceid = (SELECT MAX(invoiceid) FROM invoice);
    
    UPDATE customer
    set TOTALAMT = TOTALAMT + v_amount
    where CUSTID = v_custid;
    COMMIT;
    
  
END;

MERGE into CUSTOMER c 
  using (select CUST_ID, sum(AMOUNT) amount from INVOICE 
         where <invoice_date column> between trunc(sysdate)+7/24 and trunc(sysdate)+19/24
         group by CUST_ID) i
  on (i.CUST_ID = c.CUST_ID)
when matched then update set TOTALAMT = TOTALAMT + i.AMOUNT
when not matched then insert (CUST_ID, TOTALAMT) values (i.CUST_ID, i.AMOUNT)
/

create or replace trigger INVOICE_TRG_AIUR
after insert or update of amount on INVOICE
for each row
begin
  if inserting then
    begin
      update CUSTOMER set TOTALAMT = TOTALAMT + :new.amount where CUST_ID = :new.amount;
    exception
      when no_data_found then
        insert into CUSTOMER (CUST_ID, AMOUNT) values (:new.cust_id, :new.amount); 
  elsif updating then
    update CUSTOMER set TOTALAMT = TOTALAMT - :old.amount + :new.amount where CUST_ID = :old.cust_id;
  end if;
end;
/

select a.cust_id,a.cust_name,sum(b.amount) amount
from customer a, b invoice
where a.cust_id = b.cust_id
group by a.cust_id,a.cust_name