Bdtjtk

How can I enter the value in the line_total column that would be made by the account (sales_line.line_total = product.unit_price * sales_line.line_qty)?

I would like to have trigger or a procedure to automate this. When I enter the values in the columns, it automatically triggers the trigger, doing that calculation and inserting the result in the column sales_line.line_total.

I would also like 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?

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_line_total_ai AFTER INSERT OR UPDATE ON sales_line

FOR EACH ROW

DECLARE

    lt_value NUMBER(7,2);

BEGIN

    SELECT product.unit_price INTO lt_value FROM product;

    UPDATE sales_line

    SET line_total = :NEW.line_qty * lt_value

   -- SET line_total = (line_qty * :NEW.unit_price)   

    WHERE product_id = :NEW.product_id;



    --UPDATE sales_line

      --  SET line_total = (line_qty * :OLD.unit_price)

        --WHERE product.product_id = :OLD.product_id;

END;





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_id = :NEW.sale_id;



    UPDATE sale

        SET sale_total = (sale_total + :OLD.line_total)

        WHERE sale_id = :OLD.sale_id;

END;

You have major two issues. The first (as noted in other answers) is that the first trigger should be a before trigger that modifies the records in place. The logic for the trigger can also be simplified (as below)

The second issues is that you should be subtracting the old values in the second trigger. So:

CREATE OR REPLACE TRIGGER trg_line_total_ai

    BEFORE INSERT OR UPDATE ON sales_line

FOR EACH ROW

BEGIN

    SELECT :NEW.line_qty * p.unit_price

    INTO :NEW.line_total

    FROM product p

    WHERE p.product_id = :NEW.product_id;

END;





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_id = :NEW.sale_id;



    UPDATE sale

        SET sale_total = (sale_total - :OLD.line_total)

        WHERE sale_id = :OLD.sale_id;

END;

Note that the second trigger has two updates. This allows the sale_id to be updated. You can wrap this into a single update if you like:

    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);

You need to be very careful with expressing this logic so it works for both updates and inserts. You should probably extend this trigger to work for deletes as well.

To update sale_line you must use a before trigger, then update is not necesary over this table. Only asign value to :New.line_total.
To update sale you can do it in the dame trigger.

Use following code instead. Please note the keyword "Before" instead of After in both triggers definitions

CREATE OR REPLACE TRIGGER trg_line_total_ai BEFORE INSERT OR UPDATE ON sales_line

    FOR EACH ROW

    DECLARE

        lt_value NUMBER(7,2);

    BEGIN

        SELECT product.unit_price INTO lt_value FROM product;

        UPDATE sales_line

        SET line_total = :NEW.line_qty * lt_value

       -- SET line_total = (line_qty * :NEW.unit_price)   

        WHERE product_id = :NEW.product_id;



        --UPDATE sales_line

          --  SET line_total = (line_qty * :OLD.unit_price)

            --WHERE product.product_id = :OLD.product_id;

    END;





    CREATE OR REPLACE TRIGGER trg_sale_total_ai BEFORE INSERT OR UPDATE ON sales_line

    FOR EACH ROW

    BEGIN     

        UPDATE sale     

        SET sale_total = (sale_total + :NEW.line_total)   

        WHERE sale_id = :NEW.sale_id;



        UPDATE sale

            SET sale_total = (sale_total + :OLD.line_total)

            WHERE sale_id = :OLD.sale_id;

    END;