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

Edisson Gabriel López

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.

Frank Kulash

Hi, @Edisson Gabriel López

Whenever you have a question, please post a complete test script that the people who want to help you can run to re-create the problem and test their ideas. In this case, include CREATE TABLE statements for all the tables needed.

Remember to put a THEN after the second IF as well:

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
  THEN
    IF USER_EMAIL IS NULL
    THEN
  	  UPDATE CRPDTA.F_ASR_USERS  -- no need for alias
	  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;
	--  Don't COMMIT in triggers
  ELSE
    RAISE_APPLICATION_ERROR (-20000, 'AN8 NOT EXIST ADDRESS BOOK MASTER');
  END IF;
END;

Every IF needs a THEN.

It looks like you're missing some semicolons, too. There could be other errors, also, but I can't test it without the tables.

Anton Scheffer

correct syntax is:

if IF USER_IDENTITY IS NULL
then
 ...
else
 ...
end if;

And don't use commit inside a trigger.

Edisson Gabriel López

https://community.oracle.com/tech/developers/discussion/comment/16817062#Comment_16817062

Hi @ascheffer ,

Thanks, This is how the syntax was left, and it does not mark me an error:

create or replace TRIGGER CRPDTA.INSERTUSER 
AFTER  insert or update  
ON CRPDTA.F03012 
FOR EACH ROW
DECLARE
  USER_ID VARCHAR2(100);
  USER_IDENTITY  VARCHAR2(100);
  USER_EMAIL  VARCHAR2(100);
BEGIN
SELECT ABAN8,ABTAX INTO USER_ID,USER_IDENTITY FROM CRPDTA.F0101 WHERE ABAN8 = :new.AIAN8;
SELECT EAEMAL INTO USER_EMAIL FROM CRPDTA.F01151 WHERE EAAN8 = USER_ID;
IF USER_IDENTITY IS NULL THEN
    IF USER_EMAIL IS NULL THEN
      UPDATE CRPDTA.F_ASR_USERS F_ASR_USERS SET F_ASR_USERS.USER_EMAIL = USER_EMAIL
      WHERE F_ASR_USERS.USER_IDENTITY = :new.AIAN8;
    END IF;
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;
END;

I have a question here:

SELECT ABAN8,ABTAX INTO USER_ID,USER_IDENTITY FROM CRPDTA.F0101 WHERE ABAN8 = :new.AIAN8;

It is assumed that :new.AIAN8 when performing the insert would have this value, correct?

Example:

And also to remove the :

VALUES (USER_ID, USER_IDENTITY,USER_EMAIL, CUSTOM_HASH(USER_IDENTITY,256));

Frank Kulash

Hi, @Edisson Gabriel López

Whenever you have a question, please post a complete test script that the people who want to help you can run to re-create the problem and test their ideas. In this case, include CREATE TABLE statements for all the tables needed.

Remember to put a THEN after the second IF as well:

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
  THEN
    IF USER_EMAIL IS NULL
    THEN
  	  UPDATE CRPDTA.F_ASR_USERS  -- no need for alias
	  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;
	--  Don't COMMIT in triggers
  ELSE
    RAISE_APPLICATION_ERROR (-20000, 'AN8 NOT EXIST ADDRESS BOOK MASTER');
  END IF;
END;

Every IF needs a THEN.

It looks like you're missing some semicolons, too. There could be other errors, also, but I can't test it without the tables.