469,071 Members | 1,914 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,071 developers. It's quick & easy.

Trigger- before insert

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.
Dec 28 '06 #1
1 3852
suvam
31
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 ;
Jan 2 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Galina | last post: by
9 posts views Thread by Lauren Quantrell | last post: by
1 post views Thread by Matik | last post: by
3 posts views Thread by Michael Teja via SQLMonster.com | last post: by
2 posts views Thread by gustavo_randich | last post: by
2 posts views Thread by dean.cochrane | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.