| Subject | Re: [IB-Architect] Rows Affected inside of a stored procedure or trigger |
|---|---|
| Author | Jason Wharton |
| Post date | 2001-12-11T18:30:18Z |
Ian,
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
UPDATE.
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.
Regards,
Jason Wharton
CPS - Mesa AZ
http://www.ibobjects.com
> Why does implementing returning require a singleton operation? Isn'tIf you are going to return a "result set" from the UPDATE operation then
> it an option to return a result set?
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
UPDATE.
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 allowHow do you envision this being possible?
> 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.
> > Only problem I see is in the case of multiple record operations like anthen
> > update or delete. AFFECTED would always be an aggregate but would you
> > have to start "fetching" the update process along or get some kind ofWe are saying the same thing. I call it the rows operated upon.
> > 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.
Regards,
Jason Wharton
CPS - Mesa AZ
http://www.ibobjects.com