Re: And another one about PL/SQL

From: Rauf Sarwar <rsarwar_at_ifsna.com>
Date: 19 Jun 2002 15:05:55 -0700
Message-ID: <c2d690f2.0206191405.b92b46@posting.google.com>

"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:<aepmec$tbf$1_at_lust.ihug.co.nz>...
> 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.

Since triggers are known to be very unstable, I would highly recommend using PACKAGES.
Slap a PACKAGE against your child table which will have an INSERT procedure to handle all INSERTS against your child table. Assuming you have these tables with some data,

   SITECODE as

      site_code VARCHAR2(20), 
      category  VARCHAR2(20)

   SITEFEATURES as 
      site_code VARCHAR2(20), 
      feature  VARCHAR2(20), 
      plot VARCHAR2(10), 
      category VARCHAR2(20)

Create your package...

• CUT HERE ---- CREATE OR REPLACE PACKAGE Site_Features_Pkg AS

PROCEDURE Insert_Site_Features (

   site_ IN VARCHAR2,
   feature_ IN VARCHAR2,
   plot_ IN VARCHAR2 );

END Site_Features_Pkg;
/

SHOW ERROR CREATE OR REPLACE PACKAGE BODY Site_Features_Pkg AS

• Private Get function FUNCTION Get_Category ( site_code_ IN VARCHAR2 ) RETURN VARCHAR2;

FUNCTION Get_Category (

   site_code_ IN VARCHAR2 ) RETURN VARCHAR2 IS

   category_ VARCHAR2(20) := NULL;
   CURSOR cur_ IS

      SELECT category
      FROM sitecode
      WHERE site_code = site_code_;

BEGIN
   OPEN cur_;
   FETCH cur_ INTO category_;
   CLOSE cur_;
   RETURN category_;
END Get_Category;

PROCEDURE Insert_Site_Features (

   site_ IN VARCHAR2,
   feature_ IN VARCHAR2,
   plot_ IN VARCHAR2 )
IS

   category_ VARCHAR2(20);
BEGIN
   category_ := Get_Category(site_);

• You can put all kinds of logic here INSERT INTO SITEFEATURES ( site, feature, plot, category ) VALUES ( site_, feature_, plot_, category_ ); EXCEPTION WHEN OTHERS THEN RAISE; END Insert_Site_Features;

END Site_Features_Pkg;
/

SHOW ERROR

• CUT HERE ----

To Insert value,
BEGIN
   Site_Features_Pkg.Insert_Site_Features('<SITE>', '<FEATURE>', '<PLOT>');
END; Handle COMMIT and ROLLBACK on client side in case if error occurs.

HTH
//Rauf Sarwar Received on Wed Jun 19 2002 - 17:05:55 CDT