Update Trigger正在更新多个记录而不是单个记录

Question

我是触发器的新手，在更新触发器期间面临一个问题。场景:我有两个表[table1和table2]，当table1中的特定列发生变化时，我需要根据几个条件更新table2中的记录

这是我的触发器，

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;

但是当这个触发器被执行时，表2中的所有记录都会被更新。请提供一些建议来解决这个问题。

Answer 1

游标选择UPID = :OLD.PID。根据您所说的，table2中的所有行都具有相同的upid列值，并且它等于正在更新的table1.pid。

UPDATE语句将修改满足WHERE条件的行。这三个都是一样的:

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

意味着table2中的所有行不仅共享相同的upid，而且它们的objID和objType列值是0(至少，这是您声明的)。

此外(正如@astentx很好地观察到的)，如果您将变量命名为与列名相同，它的行为就像您将where 1 = 1(始终为真)放在一起。为局部变量使用前缀，例如l_，这样where子句就会变成

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

因此:如果以上所有内容都为真，则yes - trigger将更新table2中的所有行。