Write values whenever a new line is added via trigger

Question

Write values whenever a new line is added via trigger

Navigation

#1 by (0 votes)
#2 by (0 votes)

2

I need help solving a problem, I tried halfway, but it is not working.

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

The value of LINE_TOTAL is the product of the values of LINE_UNITS (Line Product Unit) and LINE_PRICE (Line Product Price)

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;

oracle trigger

asked by anonymous 15.07.2016 / 17:21

2 answers

How to convert a MySQL connection to MySQL? [duplicate] Error reading Like with Timestampdiff

score 0 · Answer 1

From what I understood the problem is you update the LINE_TOTAL by adding the old value with the new one inserted. So, test whether this code would solve your problem. This select you give is somewhat ambiguous, you use it and then in the where of update is the same thing that you use only his call.

Take the test and let me know how it 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;

score 0 · 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 think this will resolve by trigger, it will fire after the end of the instruction, check balance divergent and update, the performance should (almost certainly) be bad but I think it worth the test. Is there any reason to have this total in every detail line? Another solution of iso is a per sql that does a "running total" using analytical functions (OVER), but test this my "solution".