CODEWITHSUNDEEP
sqltriggersoracle11g
jjmil03
jjmil03

Reputation: 21

Creating a Trigger to update another table's column

I am trying to create a trigger that will give me the total amount for an order. I have a table ORNG_ORD_LINE which contains the multiple rows for a specific order. In my ORNG_ORDER table, I want to have a column that displays the total cost of the items that have the same order number in ORNG_ORD_LINE.

My goal for this trigger is to update the ORNG_ORDER table in the Ord_Amount Column with the calculated total of all of the Ord_Num individual line items, which are stored in the ORNG_ORD_LINE table. So if I insert or update the Ord_Num 20002, with three lines of $50 each, the Ord_Amount column should read $150. If I update or insert a row with the same order number with a new amount, or delete a row, the Ord_Amount should correct it's value to reflect the updated total.

My trigger for getting the total amount per order line is as follows:

CREATE OR REPLACE TRIGGER ORNG_ORD_LINE_TOTAL_TRIGGER 
FOR UPDATE OR INSERT ON ORNG_ORD_LINE  
COMPOUND TRIGGER 
BEFORE EACH ROW IS 
BEGIN 
    :NEW.Ord_Line_Total := :NEW.Ord_Line_Price * :NEW.Ord_Line_Quantity; 
    END BEFORE EACH ROW; 
END ORNG_ORD_LINE_TOTAL_TRIGGER;
/

This gives me the total per line of my invoice in the INV_LINE table. I want to create a trigger that will add up each individual line that has the same invoice number assigned to it, in order to get a total for the entire order, and have that number update the Ord_Amount column in the ORNG_ORDER table. I also want to make sure that if I were to delete a line from ORNG_ORD_LINE, that the trigger would update the total in ORNG_ORDER reflecting the change. This is what I have so far, but I am having some issues with the syntax, and I was hoping someone could help me fix it up:

    CREATE OR REPLACE TRIGGER ORNG_ORDER_TOTAL_TRIGGER 
    AFTER INSERT OR UPDATE ON ORNG_ORD_LINE
    DECLARE
    V_ORD_LINE_TOTAL DEC := 0
    V_ORD_NUM INTEGER := 0

    BEGIN
    --1) Get the Order Number

    SELECT :NEW.Ord_Num INTO V_ORD_NUM
    FROM ORNG_ORDER
    WHERE ORNG_ORDER.Ord_Num = :NEW.Ord_Num;

    --2) Get the sum of all lines with the matching order number and set the 
variable with that value

    V_ORD_LINE_TOTAL := 

    SELECT SUM(Ord_Line_Total)
    FROM ORNG_ORD_LINE
    WHERE Ord_Num = :NEW.Ord_Num;

    --3) Write updated total to the ORNG_ORDER tables

    UPDATE ORNG_ORDER
    SET Ord_Amount = V_ORD_LINE_TOTAL
    WHERE Ord_Num = :NEW.Ord_Num;

    END ORNG_ORDER_TOTAL_TRIGGER;
    /

I'm still trying to get the hang of this, so any help, and explaination as to what I'm doing wrong, would be greatly appreciated.

Upvotes: 0

Views: 1400

Answers (1)

jjmil03
jjmil03

Reputation: 21

The first step was to calculate internally the total price by multiplying the quantity and the price together:

CREATE OR REPLACE TRIGGER ORNG_INV_LINE_MULT
BEFORE INSERT OR UPDATE ON ORNG_INV_LINE
FOR EACH ROW
BEGIN 
:NEW.Inv_Line_Total := :NEW.Inv_Line_Price * :NEW.Inv_Line_Quantity;
END ORNG_INV_LINE_MULT;
/

This could be done with a row level trigger, because it affects the row.

The second statement needed to be a statement level trigger, or else I got a mutation error (still unsure as to why)

create or replace TRIGGER ORNG_INV_L_TTL_TRIG
AFTER INSERT OR UPDATE OR DELETE ON ORNG_INV_LINE
BEGIN
UPDATE ORNG_INVOICE SET Inv_Amount = (SELECT SUM(Inv_Line_Total) 
FROM ORNG_INV_LINE
WHERE INV_Num = ORNG_INVOICE.INV_Num);
END ORNG_INV_L_TTL_TRIG;
/

Upvotes: 0

Related Questions