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