Trigger- before insert

[gauravupreti]

Hi All,
I have a table with a col. that accepts number.

CREATE TABLE A
(ID NUMBER NOT NULL)

and another table with two columns as shown:

CREATE TABLE B
(ID NUMBER NOT NULL,
TYPE VARCHAR2(20)
)

I am getting a String "type". I have to insert the id in table A by reading corresponding id from table B for the particular type received. For this I have written following before insert trigger:

CREATE OR REPLACE TRIGGER t_A_id
BEFORE INSERT
ON A
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
SELECT B.ID
INTO :NEW.id
FROM B
WHERE TYPE IN (:NEW.id);
END;

The trigger compiles without any errors.
Now, when i try to insert into table A as
insert into A(id)
values('A')
I get "Not a valid number error"

Agreed that i am not passing a number but a string, but i am trying to change it through before insert trigger. Can you tell me how to resolve this issue.

Thanks in advance,
Gaurav.

[suvam]

Your purpose can be solved w/o going for a trigger in the following way ---->

Create Or Replace Procedure Ins_A(v_type in Varchar2) is
Begin
Insert into A(id)
Select B.id from B where Upper(Trim(B.type)) = Upper(Trim(v_type)) ;
Exception when others then
rollback;
End ;