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
var1 := 0;
IF :new.NVAL1 IS NOT NULL THEN
var2 := UPPER(:new.NVAL1);
SELECT COUNT(*) into var1 FROM LKUP_TBL
WHERE upper(FTCH) = var2;
IF var1 >= 1 THEN
INTO NTBL (name, city, state)
VALUES (:new.NAME, :new.CITY, :new.STATE);
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
Any help is appreciated thanks.