Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004

    Question Unanswered: problem of 'before insert' trigger


    My table (xyz) has a primary key which is of type NUMBER.
    Somebody (on whom, I have no control) is calling an insert statement on this table, with an auto-generated character string as the primary key value.
    Now, I have written a trigger on table xyz which will operate 'before insert'. This trigger simply ignores the primary key value given by the remote somebody and inserts an auto-generated number in the primary key field (so that our table should still treat the insert as a valid one).

    Now, the problem is this trigger works fine if the value in the original insert statement is a number. In short, even if the remote somebody tries to insert 123, the trigger ignores it and inserts the auto-generated number. But if a string type is inserted, then "invalid number" error is given. This is probably because Oracle checks that the datatypes are matching before any triggers are executed.

    Any help about how to tackle this will be immensely appreciated...


  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1

    Re: problem of 'before insert' trigger

    You are correct: Oracle rejects the insert statement as invalid before the trigger gets the chance to correct the value. It tries to implicitly convert the string to a number before inserting it, which fails if the string cannot be converted.

    Though you have "no control" over this somebody, surely if they knew what they were doing wrong they would correct their code? You could say they have no choice...

    Otherwise, here is a possible work-around.

    1) Rename the table to xyz_table (or whatever)

    2) Create view xzy as select to_char(id) id, ... from xyz_base;

    3) Create an "instead of" trigger on the view like this:

    create or replace trigger xzy_trg instead of insert on xyz
    insert into xyz_table (id, col2, ...) values (myseq.nextval, :new.col2, ...);

    Now the other user's inserts will not fail.

    Of course, this change will potentally impact any users who don't treat the ID column as a VARCHAR2!

  3. #3
    Join Date
    Jan 2004
    Thanks Tony!

    Yes, it will work the way you have said. I would surely prefer to request this somebody to change the code, by the way

    Actually that particular value comes from the Siebel's unique 'row_id' which is by default a character string....

    Thanks very much for your help..

Posting Permissions

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