Re: And another one about PL/SQL

Ah ha!

I suppose it's nice to know that I wasn't walking in a *completely* different wood, even if I was just barely stumbling around and getting wet and muddy!!

What can I say? Your solution works a treat and is everything I could have asked for. Many, many thanks.

Regards
HJR "Norman Dunbar" <Norman.Dunbar_at_lfs.co.uk> wrote in message news:E2F6A70FE45242488C865C3BC1245DA7024061F7_at_lnewton.leeds.lfs.co.uk...
> Morning/evening Howard,
>
> *untested and off top of head*
>
>
> I believe your trigger should be something like the following :
>
> create or replace trigger FEATURE_DETAILS
> before insert on SITEFEATURES
> for each row
> begin
> if (:new.category is NULL) then
> begin
> select category into :new.category from sites where
> sitecode=:NEW.sitecode;
> exception
> when NO_DATA_FOUND then
> RAISE_APPLICATION_ERROR(-20001, 'SITECODE : '|| :NEW.sitecode
> || ' is unknown');
> end;
> end if;
> end;
> /
>
> You should check to see if the category is being passed in, this saves
> on the lookup but, you might want to force the user to use the correct
> category for the site, so remove the test if so.
>
> Assuming there is no record on the sites table, then you should handle
> the exception - otherwise, the insert will fail.
>
> Have fun :o)
>
> Regards,
> Norman.
>
> -------------------------------------
> Norman Dunbar
> Database/Unix administrator
> Lynx Financial Systems Ltd.
> mailto:Norman.Dunbar_at_LFS.co.uk
> Tel: 0113 289 6265
> Fax: 0113 289 3146
> URL: http://www.Lynx-FS.com
> -------------------------------------
>
>
> -----Original Message-----
> From: Howard J. Rogers [mailto:dba_at_hjrdba.com]
> Posted At: Wednesday, June 19, 2002 11:29 AM
> Posted To: server
> Conversation: And another one about PL/SQL
> Subject: And another one about PL/SQL
>
>
> Sorry about this...
>
> If I am inserting a record into a child table, is it possible to have a
> before trigger consult the parent table to retrieve a value to insert
> into
> the child record?
>
> <SNIP>
>
>
Received on Wed Jun 19 2002 - 17:10:14 CDT