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...
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!