Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2006
    Posts
    21

    Unanswered: Invalid number error in Trigger

    i rarely, if ever use triggers, so, i've got to be doing something wrong here.
    but, i get (ora-01722) "Invalid number" on the first column which is my Sequence. Is there something i'm doing wrong here?
    thanks
    rik

    CREATE OR REPLACE TRIGGER Move_Track_Row
    AFTER UPDATE on Inventory
    FOR EACH ROW
    DECLARE
    v_seq number;
    BEGIN
    SELECT MOVE_TRACK_SEQ.NEXTVAL INTO v_seq FROM DUAL;
    INSERT INTO Move_Track(Move_TrackId,
    qty_old,
    qty_change,
    qty_new,
    length_old,
    length_change,
    length_new,
    inventoryid,
    createddate)VALUES
    (v_seq,
    :OLD.QTY,
    :OLD.QTY,
    :NEW.QTY,
    :OLD.LENGTH,
    :OLD.LENGTH,
    :NEW.LENGTH,
    :OLD.INVENTORYID,
    :OLD.CREATEDDATE,);
    END Move_Track_Row;

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You have two problems. One you should use the nextval directly and second, you have a dangling comma in your values clause. If I was you, I would have a timestamp row in my move_track table so I know when the update occured. Move_TrackID has to be defined as a number. Try the following

    Code:
     CREATE OR REPLACE TRIGGER Move_Track_Row
    AFTER UPDATE on Inventory
    FOR EACH ROW 
    BEGIN 
    INSERT INTO Move_Track(Move_TrackId,
                                         qty_old,
                                         qty_change,
                                         qty_new,
                                         length_old,
                                         length_change,
                                         length_new,
                                         inventoryid,
                                         createddate) VALUES
    		(MOVE_TRACK_SEQ.NEXTVAL,
    		:OLD.QTY,
    		:OLD.QTY,
    		:NEW.QTY,
    		:OLD.LENGTH,
    		:OLD.LENGTH,
    		:NEW.LENGTH,
    		:OLD.INVENTORYID,
    		:OLD.CREATEDDATE);
    		END Move_Track_Row;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by beilstwh
    ... you should use the nextval directly and second, you have a dangling comma in your values clause...
    You do not have to use NEXTVAL directly, It was fine with the SELECT ...FROM DUAL;
    But you do have a dangling comma.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Jan 2006
    Posts
    21

    dangling comma

    my appologies, i left that when i cut down the code for posting. there's about 50 other values in the Original Code. my fault. i actually had tried calling Nextval directly the first time. it's a personal preference, but, it didnt seem to matter which method i used.
    i still get.
    ORA-01722: invalid number
    ORA-06512: AT Move_Track_Row, line 3 (that's the For Each Row Line)
    ORA-04088: error during execution of trigger

    When i view the Stack, it highlights that First Insert Line
    "INSERT INTO Move_Track(Move_TrackId,"

    so, it's a bit confusing for me. if anyone else has encountered this, i do appreciate the feedback and advice. my First thought was the Mutating Table issue, but i dont think this applies
    thanks again guys

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Oracle error will state the line number in error as the line number where the statement begins, but the actual error may be in another line that belongs to same statement -- look at each of the values for an invalid number.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    it always helps when you post the WHOLE code and the WHOLE error message.
    What you might think as inconsequetial might actually be your error.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Jan 2006
    Posts
    21
    thanks guys once again, sorry, i should have just posted the whole thing. i'll check each value.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by LKBrwn_DBA
    You do not have to use NEXTVAL directly, It was fine with the SELECT ...FROM DUAL;
    But you do have a dangling comma.
    I didn't say that you were required to use it directly, just that it made no sense in this code to NOT use it directly.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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