CODEWITHSUNDEEP
oracle-databaseplsqltriggerssql-update
user1954970
user1954970

Reputation: 409

Update Trigger is updating multiple records instead of single record

I'm new to triggers, facing an issue during update trigger. Scenario: I have two tables [table1 and table2] when a particular column changes in table1, I need to update the table2 records based on few conditions

Here is my trigger,

create or replace TRIGGER UPDATE_TABLE1 
BEFORE UPDATE OF LOWTHRESHOLD, HIGHTHRESHOLD ON TABLE1 
for each row
  when(nvl(old.LOWTHRESHOLD,0) <> nvl(new.LOWTHRESHOLD,0) 
  OR  nvl(old.HIGHTHRESHOLD,0) <> nvl(new.HIGHTHRESHOLD,0))
  declare
  PID number(3) := nvl(:old.PID,0);
  oldLOWTH number(2) := nvl(:old.LOWTHRESHOLD,0);
  oldHIGHTH number(2) := nvl(:old.HIGHTHRESHOLD,0);
  newLOWTH number(2) := nvl(:new.LOWTHRESHOLD,0);
  newHIGHTH number(2) := nvl(:new.HIGHTHRESHOLD,0);
  ratio DOUBLE PRECISION;
  oldTHCrossed number(1) := 0;
  objID number(10) := 0;
  objType number(1) := 0;
   CURSOR profileUtil_Cursor  IS
        SELECT * FROM TABLE2 where upid = PID; 
  begin
    
  dbms_output.enable(1000);
 
  FOR v_record in profileUtil_Cursor LOOP
  ratio := v_record.UTILIZATIONRATIO;
  oldTHCrossed := v_record.THCROSSED;
  objID := v_record.objID;
  objType := v_record.objType;
 IF(ratio < newLOWTH AND ratio < newHIGHTH ) THEN
  UPDATE TABLE2 SET THCROSSED = 1, LASTUPDATED = date_to_miniseconds(sysdate) 
  WHERE objID = objID and objType = objType and upid = PID;
  ELSIF(newLOWTH <= ratio AND ratio <= newHIGHTH) THEN 
    UPDATE TABLE2 SET THCROSSED = 2, LASTUPDATED = date_to_miniseconds(sysdate) 
    WHERE objID = objID and objType = objType and upid = PID; 
  ELSIF(ratio > newHIGHTH) THEN
    UPDATE TABLE2 SET THCROSSED = 3, LASTUPDATED = date_to_miniseconds(sysdate)
    WHERE upid = PID and objID = objID and objType = objType; 
  END IF;
  END LOOP;
    IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.put_line('NOT FOUND');
    END IF;
END;

But when this trigger is executed, all the records in TABLE2 are getting updated. Please provide some pointers to solve this issue.

Upvotes: 0

Views: 120

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

Cursor selects all rows whose UPID = :OLD.PID. According to what you said, all rows in table2 have the same upid column value, and it is equal to table1.pid which is being updated right now.

UPDATE statements will modify rows that satisfy their WHERE conditions. All 3 of them are the same:

      WHERE     objID = objID         --> that's 0
            AND objType = objType     --> that's 0 as well
            AND upid = PID            --> that's "old" table1.pid

which - additionally to my 1st statement - means that all rows in table2 not only share the same upid, but their objID and objType column values are 0 (at least, that's what you declared).

Furthermore (as @astentx nicely observed), if you name variables the same as column names, it behaves as if you put where 1 = 1 which is always true. Use prefix for local variables, e.g. l_ so where clause would turn into

      WHERE     objID   = l_objID       --> that's 0
            AND objType = l_objType     --> that's 0 as well
            AND upid    = l_PID         --> that's "old" table1.pid

So: if everything above is true, then yes - trigger will update all rows in table2.

Upvotes: 1

Related Questions