Re: Insert with a relationship...

In article <MPG.139e1af3c3a43a51989690_at_news.usenetserver.com>, news4 @REMOVEbaxnet.net says...

That's EXACTLY what I needed to know - thanks to both Bob and Tom for this info. If I'm to use a trigger, then maybe I can do something like:

on insert to addr table, trigger Sproc:

  	select ID from STATE where ID = whatever was chosen on the screen
	if state.id exists then <-- (a match)
 		insert ADDR (street,city,state_id) 
		values (screen.street, screen.city, server-cursor.STATE.ID)
	else (if state doesn't exist) state_ID-error, (run through some 
elaborate top-down error handler, using ORA messages)
	endif

Or am I making more of this thing than is necessary? Is a trigger appropriate for this sort of stuff?

> I'm new to this - so here goes.
>
> Table STATE is the "one" (on the one side of the one-many relationship),
> with pri key column 'PK_State_ID'. Cool. Table ADDRESS is the "many",
> with foreign key column 'FK_State_ID' referring back to 'PK_State_ID'.
> STATE --> ADDR
>
> Am I even close with the following assumptions: ?
> 1) Make a user screen with all the Address info, have them fill it in.
> 2) When they get to the "State" field, use a drop-down and force them to
> find a valid State. (BTW - Georgia is not a valid state of any kind,
> neither is Alabama for that matter lol).
> 3) If "State" does not exist, pop up another screen to add correct
> state. Insert it. Now it should show up in #2.
> 3) Now I've got enough info to insert an ADDR record I guess.
>
> Here's my big puzzling question:
>
> If I fill in the SQL statement with all the ADDR data from my screen,
> will it then AUTOMATICALLY fill in the STATE field somehow since I've
> ensured the FK/PK constraint in ADDR? Or do I have to do manually add
> it to the insert statment, something like "insert into ADDR (street,
> city, stateid) values (screen.street, screen.city, STATE.ID)".
>
> In other words, how much is storage/retrieval is done for you during
> either the SELECT or INSERT due to the fact the constraint exists?
>
> I know this is awfully basic - sorry in advance
>
> Alex
>
>
>
>
Received on Tue May 30 2000 - 00:00:00 CDT