Bdtjtk

How can I to automate the column sale.sale_total which would be the sum of the values column sales_line.line_total. Can you make a trigger or procedure for these two questions?
can you help me?

Something like: sale.sale_total = (sale.sale_total + .sales_line.line_total).

CREATE TABLE product (

    product_id    NUMBER(4) NOT NULL,

    category_id   NUMBER(4) NOT NULL,

    p_desc        VARCHAR2(40),

    cpu           VARCHAR2(14),

    ram           VARCHAR2(14),

    capacity      VARCHAR2(14),

    screen_size   VARCHAR2(14),

    battery       VARCHAR2(14),

    unit_price    NUMBER(7, 2),

    colour        VARCHAR2(14),

    qty_stock     NUMBER(4)

);

ALTER TABLE product ADD CONSTRAINT product_pk PRIMARY KEY ( product_id );



CREATE TABLE sale (

    sale_id       NUMBER(4) NOT NULL,

    sale_date     DATE,

    customer_id   NUMBER(4) NOT NULL,

    employee_id   NUMBER(4) NOT NULL,

    sale_total    NUMBER(7, 2)

);



ALTER TABLE sale ADD CONSTRAINT sale_pk PRIMARY KEY ( sale_id );



CREATE TABLE sales_line (

    sale_id      NUMBER(4) NOT NULL,

    product_id   NUMBER(4) NOT NULL,

    line_qty     NUMBER(4),

    line_total   NUMBER(7, 2)

);



ALTER TABLE sales_line ADD CONSTRAINT index_3 PRIMARY KEY ( sale_id,

                                                            product_id );



ALTER TABLE product

    ADD CONSTRAINT product_p_category_fk FOREIGN KEY ( category_id )

        REFERENCES p_category ( category_id );



ALTER TABLE sale

    ADD CONSTRAINT sale_customer_fk FOREIGN KEY ( customer_id )

        REFERENCES customer ( customer_id );



ALTER TABLE sale

    ADD CONSTRAINT sale_employee_id_fk FOREIGN KEY ( employee_id )

        REFERENCES employee ( employee_id );



ALTER TABLE sales_line

    ADD CONSTRAINT sales_line_product_fk FOREIGN KEY ( product_id )

        REFERENCES product ( product_id );



ALTER TABLE sales_line

    ADD CONSTRAINT sales_line_sale_fk FOREIGN KEY ( sale_id )

        REFERENCES sale ( sale_id );    



**Here are the triggers I have written so far:**



CREATE OR REPLACE TRIGGER trg_sale_total_ai AFTER INSERT OR UPDATE ON sales_line

FOR EACH ROW

BEGIN

    UPDATE sale

        SET sale_total = (sale_total + :NEW.line_total)         

        WHERE sale.sale_id = :NEW.sale_id;        

END;

I tried do like @Gordon Linoff (code below), but the value in the table sale.line_total continue null. I tried with BEFORE UPDATE, the same:

CREATE OR REPLACE TRIGGER trg_sale_total_ai AFTER INSERT OR UPDATE ON sales_line

FOR EACH ROW

BEGIN

UPDATE sale     

        SET sale_total = (sale_total + 

                          (CASE WHEN sale_id = :NEW.sale_id THEN :NEW.line_total ELSE 0 END) -

                          (CASE WHEN sale_id = :OLD.sale_id THEN :OLD.line_total ELSE 0 END)

                         )  

        WHERE sale_id IN (:OLD.sale_id, :NEW.sale_id);

I just want update the sale.sale_total column with the sum of the values of the sales_line.line_total, after I insert data in the sales_line table.

**Here are the triggers I have written so far:**



CREATE OR REPLACE TRIGGER trg_sale_total_ai AFTER INSERT OR UPDATE ON sales_line

FOR EACH ROW

BEGIN

    UPDATE sale

        SET sale_total = (sale_total + :NEW.line_total)         

        WHERE sale.sale_id = :NEW.sale_id;        

END;

Thank you

Your best bet is to execute

ALTER TABLE SALE

  DROP COLUMN SALE_TOTAL;

This is not a column you want to have because, as you're just beginning to realize, maintaining it is going to be a nightmare. Instead, you want a function:

CREATE OR REPLACE FUNCTION COMPUTE_SALE_TOTAL(pinSale_id IN SALE.SALE_ID%TYPE)

  RETURN NUMBER

AS

  nSale_total  NUMBER;

BEGIN

  SELECT SUM(LINE_TOTAL)

    INTO nSale_total

    FROM SALES_LINE

    WHERE SALE_ID = pinSale_id;



  RETURN nSale_total;

END COMPUTE_SALE_TOTAL;

Then, any time you need to know what the total for a sale is, you call this function.

Best of luck.