And another one about PL/SQL

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Wed, 19 Jun 2002 12:04:33 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA7024061F7@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 - 06:04:33 CDT