CODEWITHSUNDEEP
sqloracle-databasetriggers
jjmil03
jjmil03

Reputation: 21

Trigger to update a quantity after deletion

I am trying to create a trigger named that will automatically update the product quantity on hand for a product when a corresponding product LINE row is deleted. I had no issues creating triggers that can update the same value after an insertion or an update, but for some reason this is getting me confused.

Here is what I have so far:

CREATE OR REPLACE TRIGGER UPDATE_DELETE
AFTER DELETE ON LINE
FOR EACH ROW
BEGIN  
    UPDATE PRODUCT
    SET P_QOH = P_QOH + 
    (SELECT LINE_UNITS 
    FROM LINE
    WHERE LINE.P_CODE = PRODUCT.P_CODE)
    WHERE PRODUCT.P_CODE = :NEW.P_CODE;

DBMS_OUTPUT.PUT_LINE('Product QOH updated');
END;
/

All I am trying to do is pass the value that is currently in LINE_UNITS on the row that is to be deleted, and add it to the P_QOH in the PRODUCT table, so that it reflects the correct QOH after deletion. I have tried a few different ways, but so far, no luck. When I try to delete a row, I get an error that says:

A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it. *Action: Rewrite the trigger (or function) so it does not read that table.

Here is another way I tried to do it:

CREATE OR REPLACE TRIGGER UPDATE_DELETE
AFTER DELETE ON LINE
FOR EACH ROW
BEGIN
    UPDATE PRODUCT
    SET P_QOH = P_QOH + LINE_UNITS
    WHERE PRODUCT.P_CODE = :NEW.P_CODE;

DBMS_OUTPUT.PUT_LINE('Product QOH updated');
END;
/

This gives me an error saying that LINE_UNITS is an invalid identifier - which I am assuming is because LINE_UNITS is not a part of the PRODUCT table. I have tried LINE.LINE_UNITS as well, to no avail.

I have also tried declaring a variable, setting the variable to the value of the LINE_UNITS, but that doesn't work either.

Any ideas?

Jon

Upvotes: 0

Views: 1203

Answers (1)

Justin Cave
Justin Cave

Reputation: 231741

I expect that you want

UPDATE PRODUCT
   SET P_QOH = P_QOH + :OLD.line_units
 WHERE PRODUCT.P_CODE = :OLD.P_CODE;

In a row-level trigger for a delete, the :old pseudo-record contains the row's data from before the delete operation. The :new pseudo-record will be full of NULL values (just like the :old pseudo-record is full of NULL values in a row-level insert trigger).

Upvotes: 1

Related Questions