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?

For example, a table SITES has a CATEGORY. That is supposed to be used as the default CATEGORY for each of the features that are eventually housed on a site.

For example. My site has a code BRITPK, category XXX.

I am inserting a child record that says site:BRITPK feature:BR1 plot:1, category unspecified.

I want the before insert trigger to select CATEGORY from SITES where SITECODE=the sitecode of the child record I am inserting, and thus to complete the child record.

Is that even remotely possible? Sounds a bit recursive to me. I was trying something like this:

create or replace trigger FEATURE_DETAILS before insert on SITEFEATURES
for each row
begin
 cursor get_data is
 select sitecode, category from sites where sitecode=:NEW.sitecode;  select get_data.category into :NEW.category; end;
/

But it's producing a slew of compilation errors. Which says something about my development prowess, I guess. Received on Wed Jun 19 2002 - 05:29:01 CDT