ORACLE PLSQL IF ELSEIF ELSE is not working

Question

I have an table for emails with two columns , it was the legacy, but i want to create a column with complete email address for facilitate queries and create a index, etc..

I have the following PLSQL:

create or replace TRIGGER SET_EMAIL_ADDRESS 
BEFORE INSERT OR UPDATE ON EMAIL 
FOR EACH ROW  
DECLARE

 IS_PREFIX_UPDATED BOOLEAN;
 IS_PROVIDER_UPDATED BOOLEAN;
 IS_ADDRESS_UPDATED BOOLEAN;

 IS_PREFIX_UPDATED_STR VARCHAR2(5);
 IS_PROVIDER_UPDATED_STR VARCHAR2(5);
 IS_ADDRESS_UPDATED_STR VARCHAR2(5);

BEGIN

  --COLUNA PARA DEBUG CASO NECESSARIO
  --ALTER TABLE EMAIL ADD (LOG_TRIGGER VARCHAR2(4000));

 IS_PREFIX_UPDATED := :NEW.EMAIL_PREFIX != :OLD.EMAIL_PREFIX;
 IS_PROVIDER_UPDATED := :NEW.EMAIL_PROVIDER != :OLD.EMAIL_PROVIDER;
 IS_ADDRESS_UPDATED := :NEW.EMAIL_ADDRESS != :OLD.EMAIL_ADDRESS;
 IS_PREFIX_UPDATED_STR := CASE WHEN IS_PREFIX_UPDATED THEN 'TRUE' ELSE 'FALSE' END;
 IS_PROVIDER_UPDATED_STR := CASE WHEN IS_PROVIDER_UPDATED THEN 'TRUE' ELSE 'FALSE' END;
 IS_ADDRESS_UPDATED_STR := CASE WHEN IS_ADDRESS_UPDATED THEN 'TRUE' ELSE 'FALSE' END;


  -- CASO 1 , insert ou update apenas com EMAIL_ADDRESS
  IF (NOT IS_PREFIX_UPDATED OR NOT IS_PROVIDER_UPDATED ) AND IS_ADDRESS_UPDATED THEN
      :NEW.LOG_TRIGGER := 'CASE 1 :: IS_PREFIX_UPDATED:' || IS_PREFIX_UPDATED_STR || ', IS_PROVIDER_UPDATED:' || IS_PROVIDER_UPDATED_STR ||', IS_ADDRESS_UPDATED:' || IS_ADDRESS_UPDATED_STR;
      :NEW.EMAIL_PREFIX := substr(:NEW.EMAIL_ADDRESS,1,INSTR(:NEW.EMAIL_ADDRESS,'@')-1);
      :NEW.EMAIL_PROVIDER :=  substr(:NEW.EMAIL_ADDRESS,INSTR(:NEW.EMAIL_ADDRESS,'@')+1,LENGTH(:NEW.EMAIL_ADDRESS));

  -- CASO 2 , insert ou update apenas com PREFIX E PROVIDER
  ELSIF (IS_PREFIX_UPDATED OR IS_PROVIDER_UPDATED) AND NOT IS_ADDRESS_UPDATED THEN
     :NEW.LOG_TRIGGER := 'CASE 2 :: IS_PREFIX_UPDATED:' || IS_PREFIX_UPDATED_STR || ', IS_PROVIDER_UPDATED:' || IS_PROVIDER_UPDATED_STR ||', IS_ADDRESS_UPDATED:' || IS_ADDRESS_UPDATED_STR;
     :NEW.EMAIL_ADDRESS := :NEW.EMAIL_PREFIX || '@' || :NEW.EMAIL_PROVIDER;
  ELSE 
     :NEW.LOG_TRIGGER := 'ERROR :: IS_PREFIX_UPDATED:' || IS_PREFIX_UPDATED_STR || ', IS_PROVIDER_UPDATED:' || IS_PROVIDER_UPDATED_STR ||', IS_ADDRESS_UPDATED:' || IS_ADDRESS_UPDATED_STR;
  END IF;
END;

Note: I create a column called "LOG_TRIGGER" just for debug.

Considering that :OLD value is :

EMAILL_ID | EMAIL_PREFIX | EMAIL_PROVIDER | EMAIL_ADDRESS | LOG_TRIGGER | 
763997    | NULL         | NULL           | [email protected] | NULL        |

I'm trying to execute the following UPDATE statement:

UPDATE EMAIL SET EMAIL_ADDRESS ='[email protected]' WHERE EMAIL_ID = 763997;

So, the result is :

EMAILL_ID | EMAIL_PREFIX | EMAIL_PROVIDER | EMAIL_ADDRESS |
763997    | NULL         | NULL           | [email protected] |

| LOG_TRIGGER | 
| ERRO :: IS_PREFIX_UPDATED:FALSE, IS_PROVIDER_UPDATED:FALSE, IS_ADDRESS_UPDATED:TRUE |

I don´t understand why it isn´t working =P

MT0 · Accepted Answer · 2016-06-25 01:11:08Z

A BOOLEAN data type can have 3 values: TRUE, FALSE or NULL.

You are comparing NULL values and so the boolean is being set to NULL rather than TRUE or FALSE.

SET SERVEROUTPUT ON;
SET DEFINE OFF;

DECLARE
  a BOOLEAN := NULL;
  b BOOLEAN := NULL;
  c BOOLEAN := TRUE;

  PROCEDURE printState( id IN VARCHAR2, b IN BOOLEAN )
  IS BEGIN
    IF b IS NULL THEN DBMS_OUTPUT.PUT_LINE( id || ' IS NULL' );
    ELSIF b      THEN DBMS_OUTPUT.PUT_LINE( id || ' IS TRUE' );
                 ELSE DBMS_OUTPUT.PUT_LINE( id || ' IS FALSE' );
    END IF;
  END;
BEGIN
  printState( 'a', a );
  printState( 'b', b );
  printState( 'c', c );
  printState( '(!a|!b)&c', ( NOT a OR NOT b ) AND c );
END;
/

Outputs:

a IS NULL
b IS NULL
c IS TRUE
(!a|!b)&c IS NULL

But changing it to:

  a BOOLEAN := FALSE;
  b BOOLEAN := FALSE;
  c BOOLEAN := TRUE;

Outputs:

a IS FALSE
b IS FALSE
c IS TRUE
(!a|!b)&c IS TRUE

You want to do:

IS_PREFIX_UPDATED :=
     ( :NEW.EMAIL_PREFIX IS NULL AND :OLD.EMAIL_PREFIX IS NOT NULL )
  OR ( :NEW.EMAIL_PREFIX IS NOT NULL AND :OLD.EMAIL_PREFIX IS NULL )
  OR ( :NEW.EMAIL_PREFIX IS NOT NULL AND :OLD.EMAIL_PREFIX IS NOT NULL AND :NEW.EMAIL_PREFIX != :OLD.EMAIL_PREFIX ) ;

Collectives™ on Stack Overflow

ORACLE PLSQL IF ELSEIF ELSE is not working

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related