At 15.28 30/11/2001 -0500, you wrote:

>At 04:55 PM 11/29/01 -0700, Jason Wharton wrote:
> >Not sure if this belongs here or in priorities. I am interested in getting
> >into an architectural discussion about the possibility of implementing this
> >feature so I decided to include it here.
> >
> >I think it would be VERY useful to have a trigger/stored procedure
> >language enhancement such that you could do something like this:
> >
> >CREATE PROCEDURE APROC( KEY INTEGER, VAL VARCHAR( 10 ))
> >AS
> >DECLARE VARIABLE ROWS_AFFECTED INTEGER;
> >BEGIN
> >
> > UPDATE ATBL
> > SET ACOL = :VAL
> > WHERE AKEY = :KEY
> > AFFECTED :ROWS_AFFECTED;
> >
> > IF ( ROWS_AFFECTED = 0 ) THEN
> > INSERT INTO ATBL ( AKEY, ACOL ) VALUES ( :KEY, :VAL );
> >
> >END
> >
> >>From my understanding of BLR it would be very trivial to conjure up a
> way to
> >pull this off. Is all the Affected return value needs to represent is how
> >many rows were walked through during the operation. That is simple a counter
> >in the rse loop.
> >
>
>Jason, what is your goal? Do you want programatic access to the
>number of affected rows or a better way to handle conditional
>update else insert?

I think that even if the rows affected can be useful for update/insert
situation,
it can be useful also for some other uses e.g.
update on table 1
if on table 1 the record is not present, I don't insert it directly
instead I run a procedure to do it.

>If it is the later, a REPLACE verb (syntactically parallel to INSERT)
>is a very good solution for the usage and efficiency.

REPLACE is really useful, but I think that the one does not exclude the other.
My two cents,
Marco