| How to insert a String type parameter while it should be Integer? [message #36387] |
Fri, 23 November 2001 11:32  |
Andrew Ho
Messages: 2
Registered: November 2001
|
Junior Member |
|
|
I've two simple tables:
create table table1(
name VARCHAR2(10),
ID INTEGER,
unique(name),
primary key(ID));
create table table2(
name VARCHAR2(10),
table1ID INTEGER,
primary key(name),
foreign key(table1ID) references table1);
In short, table2 has a foreign key referencing table1, very straight forward.
Let's say table1 has a record:
name id
---- --
abc 1
I'm writing JDBC methods for accessing these tables. The defined interface for inserting a new table2 value is:
AddTable2( String name, String table1ID);
In order to insert a new record into table2 referencing this record, I should write:-
insert into table2 (name, table1ID) ('whatever', 1);
and I can easily map 'abc' and the value 1 using a simple query.
However, the requirement states that in order to avoid unneccessary JDBC overhead, triggers are asked to do the mapping task.
So I tried writing a BEFORE INSERT trigger so that the sql statement
insert into table2 (name,table1ID) ('whatever', 'abc');
would still work. But before the trigger is invoked the statement is rejected by the dataype checking, cos I placed a string to a place where an integer should be.
Does anyone know if there's a work around for it? Or am I misreading the requirements?
I've been stuck on this for a long time, so I really appreciate any input.
Thanx again.
Andrew
----------------------------------------------------------------------
|
|
|
|
| Re: How to insert a String type parameter while it should be Integer? [message #36388 is a reply to message #36387] |
Fri, 23 November 2001 13:09  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
I's say the word "trigger" is misleading. I think what you need is a procedure which would accept 'whatever' and 'abc' as paramters. Then step #1 is for the proc to query table to find out the id corresponding to 'abc'. Now that you have that id, insert the child record. In pseudo-code:
proc add_tab2_child (
p_child_field in varchar2,
p_parent_descrip in varchar2) is
v_id number;
begin
begin
select id into v_id from table 1 where descrip=p_parent_descrip;
exception
when no_data_found then error 'couldnt find id for abc'
when too_many_rows then error 'abc is not a unique description - more than 1 row found'
end;
insert into table2 (name,table1ID) values p_child_field, v_id);
exception
when ...
end;
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
