Forum Stats

  • 3,815,828 Users
  • 2,259,096 Discussions
  • 7,893,264 Comments

Discussions

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

Edisson Gabriel López
Edisson Gabriel López Member Posts: 52 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.

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,911 Red Diamond

    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.

Answers

  • Anton Scheffer
    Anton Scheffer Member Posts: 1,950 Gold Trophy

    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
    Edisson Gabriel López Member Posts: 52 Green Ribbon
    edited Nov 25, 2021 10:36PM

    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
    Frank Kulash Member, Moderator Posts: 41,911 Red Diamond

    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.