IF THEN ELSE NESTED PL/SQL ORACLE

Question

I am a beginner using PL/SQL in oracle database, I just want to develop the coding that has been exist before. I want to put new condition and statement IF THEN ELSE NESTED after this code ELSIF updating THEN H_TYP := 'U'; But I am stuck with the my code, I have not yet find the way to fixing my code.

Here is my code;

create or replace TRIGGER TMCI_SUB_ITEM_MASTER_TR_R 
AFTER DELETE OR INSERT OR UPDATE ON TMCI_SUB_ITEM_MASTER 
REFERENCING OLD AS OLD NEW AS NEW 
FOR EACH ROW
BEGIN
DECLARE
 H_ID TMCI_SUB_ITEM_MASTER_R.HST_ID%TYPE;
 H_TYP TMCI_SUB_ITEM_MASTER_R.TSK%TYPE;
 rdate DATE;
 t_max_rev TMCI_SUB_ITEM_MASTER_R.REV%TYPE;
 H_INS_USR_ID TMCI_SUB_ITEM_MASTER_R.INS_USR_ID%TYPE;

 BEGIN
  rdate := SYSDATE;

IF INSERTING THEN H_TYP := 'I';
   ...

ELSIF updating THEN H_TYP := 'U';
   IF H_INS_USR_ID = 'SL01' THEN
      SELECT NVL(MAX(Rev), 0) INTO t_max_rev FROM TMCI_SUB_ITEM_MASTER_R WHERE ITM_CD = :old.ITM_CD;
      INSERT INTO TMCI_SUB_ITEM_MASTER_R
        VALUES( H_ID,
                H_TYP,
                :old.ITM_CD,
                CASE WHEN :old.ITM_NM <> :new.ITM_NM THEN CONCAT(:new.ITM_NM,'')
                     ELSE :new.ITM_NM
                END);
   ELSE
      SELECT NVL(MAX(Rev), 0) INTO t_max_rev FROM TMCI_SUB_ITEM_MASTER_R WHERE ITM_CD = :old.ITM_CD;
      INSERT INTO TMCI_SUB_ITEM_MASTER_R
        VALUES( H_ID,
                H_TYP,
                :old.ITM_CD,
                CASE WHEN :old.ITM_NM <> :new.ITM_NM THEN CONCAT(:new.ITM_NM,'*')
                     ELSE :new.ITM_NM
                END);
   END IF;
ELSIF deleting THEN H_TYP := 'D';
   ...
END IF;
  END;
END;

If I login by SL01 result will always read in the last statement (ELSE ...). It should be execute the first statement. I need a help for fixing this problem.

Connor McDonald · Accepted Answer · 2020-08-17 07:14:38Z

1

It looks like you are never setting H_INS_USR_ID to any value. I assume it is meant to the user for the incoming row? If that is the case, then

H_INS_USR_ID TMCI_SUB_ITEM_MASTER_R.INS_USR_ID%TYPE;

becomes

H_INS_USR_ID TMCI_SUB_ITEM_MASTER_R.INS_USR_ID%TYPE := :new.INS_USR_ID;

answered Aug 17, 2020 at 7:14

Connor McDonald

11.8k1 gold badge14 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sujitmohanty30 Over a year ago

Agree to the part H_INS_USR_ID has no value, however trigger is on table TMCI_SUB_ITEM_MASTER and the type he is referring is from TMCI_SUB_ITEM_MASTER_R .

Connor McDonald Over a year ago

Ah...my bad eyesight :-)

Collectives™ on Stack Overflow

IF THEN ELSE NESTED PL/SQL ORACLE

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related