Trigger hekp needed!

[sheed]

I have the following trigger that is not working. In the following trigger the statement:

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

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

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;
  
END FETCHINGVAL;
/

Can someone please help why is that I always get the count 0. How can I fix this. Thanks

 

[SantaMufasa]

Sheed,

Before we can help, we need to have evidence of SVALD.NVAL1's :new value for any UPDATE or INSERT into SVALD and the distinct values of LKUP_TBL.FTCH.

The explanation for the "0" behaviour is one or more of the following conditions:

* :new.NVAL1 IS NULL
or
* upper(FTCH) <> :new.NVAL1

Presumably, you are doing all of this on a test schema of tables. If so, you can glean additional information by making the initial value of var1 := 999999. You should then see at least a new row in NTBL as a result. Don't be surprised, however, if all values on the new row are NULL.

Let us know your findings...then we can help.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.