Ignoring any previous references to multiple programs... The
originally described program would just as well use an update CURSOR,
FETCH the row, then optionally UPDATE WHERE CURRENT OF according to the
value of the COUNTER column [not being maximum] and informing of the
impending maximum if appropriate, or informing of the already-maximum.
The row would be retrieved just once and optionally updated, thus no
need for a re-retrieval of the row; albeit the work would not have been
deferred to a single non-cursor UPDATE, but that is what currently is
However there was actually reference to other programs and an
apparent implication they should similarly effect the desired
notification of the maximum value. Thus triggers [or constraints] are
often a better choice.
As to making a TRIGGER do the work to communicate the value,
non-exception messages would be better; an exception from the trigger
only for the maximum already met. In a prior message I referred to
/tricks/ that could be performed by a trigger to communicate the
information back to the application, and messaging is one of them. That
is somewhat trickery because the application must actively obtain the
information; knowing to look for and remove the messaging when updating
that file with the trigger for which now there is a contract between all
apps and that trigger. That does not make for a good design because the
trigger would best act according to the data and be done. The use of
Global Variables in the SQL statements presumably could make the use of
an effective return value seamless; I have not used them, and I doubt
they are very worthwhile for a client interface.
Anyhow, a TRIGGER would be better to implement the notification as a
"business rule" rather than the application ever having to know the
value of the COUNTER column [in order to make that decision at the
application level]. That is, a trigger that would not be used to
communicate the value of the COUNTER back to the application.
On 13-Apr-2012 08:22 , Thomas Garvey wrote:
I'm just trying to see if there is a way to get the value just
updated by an SQL statement, without having to re-retrieve the
record. I mean, I could just resort to native data retrieval but
was trying to stay with SQL.
rob on Friday, April 13, 2012 9:26 AM wrote:
Why would the update program need to perform the logic? Why
couldn't the trigger program? Seriously. What happens if you adopt
a batch program, web interface, C/S interface, etc; do you still
want the logic in each of these programs?
But if it just HAS to be the update program you could study
throwing and catching exceptions.
<<SNIP intermediate replies>>
On 11-Apr-2012 09:47 , Thomas Garvey wrote:
I'm using embedded SQL to update a counter in a database field,
but would also like to know the new value of that counter
without having to retrieve the newly updated record.
For example, using embedded SQL in my RPGLE, as follows...
set counter = counter + 1
where keyfield = :something
I need to be able to control subsequent logic if the counter
has reached a certain limit. I'd like to not have to
re-retrieve the record to find out if the limit has been