CODEWITHSUNDEEP
oracle-databaseplsql
Nurman Ade
Nurman Ade

Reputation: 31

IF THEN ELSE NESTED PL/SQL ORACLE

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.

Upvotes: 1

Views: 316

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11591

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;

Upvotes: 1

Related Questions