Forum Stats

  • 3,817,244 Users
  • 2,259,294 Discussions
  • 7,893,711 Comments

Discussions

How can I prevent my update from modifying all the columns?

Edisson Gabriel López
Edisson Gabriel López Member Posts: 53 Green Ribbon
edited Nov 26, 2021 3:05PM in SQL & PL/SQL

Hello,

I have the following problem with my trigger, 

I am updating a user, but instead of only modifying 1 record related to the user, it modifies all the records in the table.

create or replace TRIGGER CRPDTA.INSERTUSEEMAIL
AFTER  insert or update  
ON CRPDTA.F01151
FOR EACH ROW
DECLARE
  USER_ID_IN VARCHAR2(100);
  USER_IDENTITY  VARCHAR2(100);
  EXIST_AN8 VARCHAR2(100);
  EXIST_ID VARCHAR2(100);
BEGIN
SELECT 
ABAN8,
ABTAX,
(SELECT count(AIAN8) FROM CRPDTA.F03012 WHERE F03012.AIAN8 = :new.EAAN8) EXT_AN8,
(SELECT count(F_ASR_USERS.USER_ID) FROM CRPDTA.F_ASR_USERS WHERE F_ASR_USERS.USER_ID = :new.EAAN8) EXT
INTO USER_ID_IN,USER_IDENTITY,EXIST_AN8,EXIST_ID 
FROM CRPDTA.F0101 WHERE ABAN8 = :new.EAAN8;
IF EXIST_AN8 = 1
  THEN
    IF EXIST_ID = 1
        THEN
          UPDATE CRPDTA.F_ASR_USERS
          SET F_ASR_USERS.USER_EMAIL = :new.EAEMAL
          WHERE F_ASR_USERS.USER_IDENTITY = USER_IDENTITY;
    ELSE 
          INSERT INTO CRPDTA.F_ASR_USERS (USER_ID,  USER_IDENTITY, USER_EMAIL, USER_PASSWORD) 
          VALUES (USER_ID_IN, USER_IDENTITY,:new.EAEMAL, CUSTOM_HASH (USER_IDENTITY, 256));
    
    END IF;
END IF;  
END;


As you can see, all records are affected by the update

Here I will update the email:

I don't know if the problem is FOR EACH ROW:

Oracle version: 19.0.0.0.0 - Production

Best Answer

Answers