Subject Re: [IB-Architect] Rows Affected inside of a stored procedure or trigger
Author Jason Wharton

> Why does implementing returning require a singleton operation? Isn't
> it an option to return a result set?

If you are going to return a "result set" from the UPDATE operation then
would you return a result row for each record touched as it is touched? In
which case your AFFECTED would always be one in the output row which would
kind of be pointless because you are getting a row back as a result that you
can just count yourself.

So, my point here is, if you are going to have a RETURNS record for each one
updated you don't need the "rows affected" because you are given a hook for
each one affected and can just count them yourself. In your case, I do a FOR
SELECT and put a singleton UPDATE inside it using the DB_KEY which is very
fast. It's just the syntax isn't as clean. It forces you to use all sorts of
temporary memory variables to transfer the values from the SELECT to the

In my situation, I desired that the update execute in entirety on the server
and return as a single output the number or rows operated upon. Then, you
have a single figure which holds the number of records hit and all the rest
stays internal and presumably more efficient in its operation.

> The current client tools would need modification anyway to allow
> either option to take place as I don't believe that an DML statement can
> return anything. Allowing it to return a result set would mean that any
> client tool would work without changing the interface.

How do you envision this being possible?

> > Only problem I see is in the case of multiple record operations like an
> > update or delete. AFFECTED would always be an aggregate but would you
> > have to start "fetching" the update process along or get some kind of
> > aggregate on the operation for other columns.
> I think that the best approach for this type of problem is to return
> the number of primary records read. ie ignore any records affected by
> triggers etc.

We are saying the same thing. I call it the rows operated upon.

Jason Wharton
CPS - Mesa AZ