Save values whenever a new line is added via Trigger

Question

I need help to solve a problem, I tried it halfway, but it’s not working.

I have two tables to which I have to perform this activity and create a trigger called trg_line_total to save the value of line_total on the table LINE whenever a new line is added in LINE.

The value of LINE_TOTAL is the product of the values of LINE_UNITS(Product unit of the line) and LINE_PRICE(Price of the product of the line)

CREATE TABLE INVOICE( 
  INV_NUMBER number primary key, 
  INV_DATE date default SYSDATE not null,
  INV_SUBTOTAL NUMBER(9,2)DEFAULT 0.00, 
  INV_TAX NUMBER(9,2)DEFAULT 0.00, 
  INV_TOTAL NUMBER(9,2)DEFAULT 0.00, 
 CONSTRAINT INV_CK1 CHECK(INV_DATE>TO_DATE('01-JAN-2008','DD-MON-YYYY')));

CREATE TABLE LINE( 
  INV_NUMBER number not null, 
  LINE_NUMBER number not null,
  LINE_UNITS Number default 0 not null, 
  LINE_PRICE number(9,2) default 0.00 not null, 
  LINE_TOTALE number(9,2) default 0.00 not null, 
  primary key(INV_NUMBER,LINE_NUMBER),
  foreign key(INV_NUMBER)references INVOICE on delete cascade);

CREATE OR REPLACE TRIGGER trg_line_total
  AFTER INSERT ON line
  FOR EACH ROW
DECLARE
  total  line.line_totale%TYPE;
  codigo line.inv_number%TYPE;
BEGIN
  SELECT inv_number
    INTO codigo
    FROM invoice
   WHERE inv_number = :new.inv_number;
  total := :new.line_units * :new.line_price;
  UPDATE line
     SET line_totale = line_totale + total
   WHERE inv_number = codigo;
END;

Answer 1

From what I understand the problem is you update the LINE_TOTAL adding the old value with the new one inserted. Like this, test if this code would solve your problem. This select you give is something ambiguous, you use it and then in the update Where is the same thing you use only his call.

Take the test and let us know how you behaved and solved.

create or replace TRIGGER trg_line_total AFTER INSERT ON LINE
FOR EACH ROW
BEGIN
    update LINE
   set LINE_TOTAL = :old.LINE_TOTAL + (:new.LINE_UNITS * :new.LINE_PRICE);
   where INV_NUMBER = :new.INV_NUMBER;
END;

Test on Where INV_NUMBER = :new.INV_NUMBER * :new. ; or Where INV_NUMBER = :old.INV_NUMBER;

Answer 2

create or replace TRIGGER trg_line_total AFTER INSERT ON LINE 
-- STATEMENT NAO "EACH ROW"
BEGIN
    --MONTA UM CURSOR (RTOTAL) PARA TOTALIZAR OS VALORES 
    for RTOTAL in (SELECT line.inv_number , SUM(line_units * :new.line_price) TOTAL
              FROM invoice , line
              WHERE invoice.inv_number = line.inv_number
              GROUP BY line.inv_number)
    LOOP    
        -- ATUALIZA AS LINHAS COM OS TOTAIS APURADOS (SE O VALOR FOR DIFERENTE)   
        UPDATE line
               SET line_total = RTOTAL .total
        WHERE inv_number = RTOTAL.inv_number
        AND   line_total <> RTOTAL.total;
    END LOOP;   
END;

I believe this resolves by Rigger , will be fired after the end of the instruction , checks divergent balance and updates , the performance should (almost certainly) get bad but I believe ditch the test. Is there any reason to have this total in every detail line ? Another solution of obrter iso and by an sql that makes a "running total" using analytic functions (OVER) but make the test of this my "solution".