Re: SQL%notfound

From: Andrew Finkenstadt <andy_at_vistachrome.com>
Date: Tue, 15 Jun 1993 23:18:17 GMT
Message-ID: <C8orEH.B50_at_vistachrome.com>

moswald_at_cwis.unomaha.edu (Mike Oswald) writes:
>I thought by writing the following it would prevent the user from deleting
>records if the record exists in a related table but it doesn't work. The
>coded segment can be found in SQL*Forms v3, KEY-DELREC trigger.
> declare
> cursor CNV_CUR is
> SELECT *
-- why select all rows when just a 0 will do?

> FROM <table_1> a
> WHERE a.field = :<block>.<field> ;
> begin
-- Insert here to open the cursor, check if it found anything, -- and then close it.

> if SQL%notfound then
-- change this to cnv_cur%notfound, because SQL%notfound is for

> delete_record ;
> else
> <blah>
> end if ;

• close it
  > end ;
  

So, the correct trigger could be:

declare
  dummy number;
  cursor CNV_CUR is
    SELECT 0

      FROM <table_1> a
     WHERE a.field = :<block>.<field> ;

begin
  open cnv_cur;
  fetch cnv_cur into dummy;
  if cnv_cur%notfound then
    delete_record ;
  else
    <blah>
  end if ;
  close cnv_cur;
end ;

-- 
Andrew Finkenstadt, Vista-Chrome, Inc., Homes & Land Publishing Corporation
GEnie Unix RoundTable Manager, andy_at_vistachrome.com, andy_at_genie.geis.com.
"[The author] neither accidentally nor intentionally omits or includes 
anything that could support a preconceived thesis." - C&EN 21-DEC-92 p.72

Received on Wed Jun 16 1993 - 01:18:17 CEST