Oracle Trigger not working

[rockets12345]

I have the following trigger that is not working. In the following table

SELECT COUNT(*) into var1 FROM LKUP_TBL WHERE upper(FTCH) = var2;

always returns me 0 even though I have the values in.

On SVALD table whenever a record is inserted or updated my trigger is fired that checks if the value inserted in NVAL1 column of
SVALD table does exists in LKUP_TBL. And if it exists I am inserting in the NTBL table. But some how the second condition never gets executed
because var1 is 0.

CREATE OR REPLACE TRIGGER FETCHINGVAL
AFTER INSERT OR UPDATE ON SVALD
FOR EACH ROW
DECLARE
var1 NUMBER;
var2 VARCHAR2(10);
BEGIN

var1 := 0;

IF :new.NVAL1 IS NOT NULL THEN
   
   var2 := UPPER(:new.NVAL1);

   SELECT COUNT(*) into var1 FROM LKUP_TBL
   WHERE upper(FTCH) = var2;
   
END IF;

IF var1 >= 1 THEN   
    INSERT 
    INTO NTBL (name, city, state) 
    VALUES (:new.NAME, :new.CITY, :new.STATE);
END IF;
   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END FETCHINGVAL;
/

Any help is appreciated thanks.

[chazzy]

Well, for one have you checked the data? Just as a matter of testing, i would do something like this:

SELECT COUNT(*), upper(FTCH) FTCHu FROM LKUP_TBL GROUP BY FTCH;

make sure that you actually have appropriate data going in. then try to insert an appropriate data.

the only other thing i can think of is using bind variables, but that is probably not the issue in question.

[rockets12345]

Yes I have tested the data and it works fine. Also int he following query if I replace var2 with the hardcoded value it works fine, but I need to be dynamic:

SELECT COUNT(*) into var1 FROM LKUP_TBL
   WHERE upper(FTCH) = var2;

The only other thing is that datatypes of columsn is different i.e.

NVAL VHAR
FTCH VARCHAR2

[chazzy]

well if FTCH is char (i think that's what you meant) then maybe declare var2 as char as well? it should be able to translate over though...

that would be my only guess though. what happens with this definition:

CREATE OR REPLACE TRIGGER FETCHINGVAL
AFTER INSERT OR UPDATE ON SVALD
REFERENCING NEW AS NEW
FOR EACH ROW
DECLARE
var1 NUMBER;
BEGIN

var1 := 0;

IF :new.NVAL1 IS NOT NULL THEN
   
   SELECT COUNT(*) into var1 FROM LKUP_TBL WHERE to_char(upper(FTCH)) = to_char(UPPER(:new.NVAL1));
   
END IF;

IF var1 >= 1 THEN   
    INSERT 
    INTO NTBL (name, city, state) 
    VALUES (:new.NAME, :new.CITY, :new.STATE);
END IF;
   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END FETCHINGVAL;
/

I dont' know if these changes are going to work, they might. i believe that the to_char's will force them to take the same type, little rusty with my triggers.