Forum Stats

  • 3,767,984 Users
  • 2,252,736 Discussions
  • 7,874,399 Comments

Discussions

How can I insert into a table after an insert event?

Edisson Gabriel López
Edisson Gabriel López Member Posts: 27 Green Ribbon

Hello,

This is the trigger I am using that listens for an INSERT or UPDATE event from table CRPDTA.F03012 where it should insert the data in table CRPDTA.F_ASR_USERS the fields F_ASR_USERS.USER_ID(F03012.AIAN8), F_ASR_USERS.USER_IDENTITY(F0101.ABTAX), F_ASR_USERS.USER_EMAIL(F01151.EAEMAL) and F_ASR_USERS.USER_PASSWORD(F0101.ABTAX), this is my trigger:

create or replace TRIGGER CRPDTA.INSERTUSER 
AFTER insert or update  
ON CRPDTA.F03012 
FOR EACH ROW
DECLARE
 USER_ID NUMBER := :NEW.AIAN8;
 USER_IDENTITY VARCHAR2(100);
 USER_EMAIL VARCHAR2(100);
BEGIN
SELECT ABTAX INTO USER_IDENTITY FROM CRPDTA.F0101 WHERE ABAN8 = :USER_ID;
SELECT EAEMAL INTO USER_EMAIL FROM CRPDTA.F01151 WHERE ABAN8 = :USER_ID;
IF USER_IDENTITY IS NULL
  IF USER_EMAIL IS NULL
   UPDATE CRPDTA.F_ASR_USERS F_ASR_USERS SET F_ASR_USERS.USER_EMAIL = :USER_EMAIL 
   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, :USER_IDENTITY,:USER_EMAIL, CUSTOM_HASH(:USER_IDENTITY,256))
  END IF;
  COMMIT;
ELSE
 RAISE_APPLICATION_ERROR(-20000,'AN8 NOT EXIST ADDRESS BOOK MASTER')
END IF;
END;

The problem is that I have a bug, and I'm not sure if it works the way I put it, and I can't find in forums more or less the case I have.

And this is the error I have:

Oracle version: 19.0.0.0.0 - Production

I hope you can help me, thank you.

Answers