CODEWITHSUNDEEP
oracle-databaseplsql
StevieP
StevieP

Reputation: 131

PL/SQL Update Trigger Updates All Rows

New to working with PL/SQL and trying to create a statement level trigger that will change the 'Reorder' value to 'Yes' when the product quantity (p_qoh) is either less than 10 or less than two times the product minimum (p_min). And if that's not the case, then to change the 'Reorder' value to 'No'. My problem is that when I perform an update for a specific product, it changes the reorder value of all rows instead of the one I'm specifying. Can't seem to figure out where I'm going wrong, think I've been staring at it too long, any help is greatly appreciated.

CREATE OR REPLACE TRIGGER TRG_AlterProd
AFTER INSERT OR UPDATE OF p_qoh, p_min ON product
DECLARE
  v_p_min  product.p_min%type;
  v_p_qoh  product.p_qoh%type;
CURSOR v_cursor IS SELECT p_min, p_qoh FROM product;
BEGIN
  OPEN v_cursor;
LOOP
  FETCH v_cursor INTO v_p_min, v_p_qoh;
  EXIT WHEN v_cursor%NOTFOUND;
IF v_p_qoh < (v_p_min * 2) OR v_p_qoh < 10 THEN
 UPDATE product SET p_reorder = 'Yes';
ELSE
  UPDATE product SET p_reorder = 'No';
END IF;
END LOOP;
END;
/

Upvotes: 3

Views: 1960

Answers (3)

svaratech
svaratech

Reputation: 261

@StevieP, If you need to commit inside a trigger, you may want to consider doing it as Autonomous Transaction.

Also, sorry if my understanding of your problem statement is wrong, but your it sounded to me like a row level trigger - are you only updating the current row or are you scanning the entire table to change status on several rows? If it's on current row, @OldProgrammer's solution seems right.

And I am just curious, if you do an UPDATE statement inside the trigger on the same table, wouldn't it generate (recursive) trigger(s)? I haven't done statement triggers like this, so sorry if this is not the expected trigger behavior.

To me a statement trigger would make more sense, if the trigger was on say, sales table, when a product is sold (inserted into sales table), it will trigger the corresponding product id records to be updated (to REORDER) in Product table. That will prevent recursion danger also.

Upvotes: 0

OldProgrammer
OldProgrammer

Reputation: 12159

Whoaa, this is not how you do triggers.

1 - Read the Oracle Trigger Documentation

2 - (almost) Never do a commit in a trigger. That is the domain of the calling application.

3 - There is no need to select anything related to product. You already have the product record at hand with the :new and :old pseudo records. Just update the column value in :new as needed. Example below (not checked for syntax errors, etc.);

CREATE OR REPLACE TRIGGER TRG_AlterProd
BEFORE INSERT OR UPDATE OF p_qoh, p_min ON product
FOR EACH ROW
BEGIN

IF :new.p_qoh < (:new.p_min * 2) OR :new.p_qoh < 10 THEN
  :new.p_reorder = 'Yes';
ELSE
  :new p_reorder = 'No';
END IF;
END;

Upvotes: 1

Mohammed Aouf Zouag
Mohammed Aouf Zouag

Reputation: 17132

The update command :

UPDATE product SET p_reorder = 'Yes';

updates all of your rows because you are not specifying a WHERE clause. What you can do is to retrieve the product's id (product_id) using your cursor and save it so that you would use it this way:

UPDATE product SET p_reorder = 'Yes' WHERE id = product_id;

Upvotes: 2

Related Questions