www.webdeveloper.com
Results 1 to 4 of 4

Thread: Oracle Trigger not working

  1. #1
    Join Date
    Sep 2005
    Posts
    10

    Oracle Trigger not working

    I have the following trigger that is not working. In the following table
    Code:
    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.



    Code:
    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.

  2. #2
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    Well, for one have you checked the data? Just as a matter of testing, i would do something like this:

    Code:
    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.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  3. #3
    Join Date
    Sep 2005
    Posts
    10
    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:

    Code:
    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

  4. #4
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    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:

    Code:
    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.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles