On 30-May-2014 10:15 -0500, Jeff Young wrote:
I have a file that I want to monitor for a change to a specific
field. If this field becomes negative, I want to set it to zero.
I can image almost no way, how, that could be a valid business rule.
The file is updated by many programs and is my inventory master, so
there are a large number of transactions all day.
I would expect that if any program was setting the value to less than
zero, the program almost surely has a problem. That, or negative values
are valid, but perhaps only if representative of the SUM of those items
on back-order; thus the app or a trigger ensures that corresponding data
is kept in sync.?
Is a trigger the best way to do this?
As the scenario is stated originally, yes, that is the best way. And
the only way to get the effect, without ensuring no such updates are
capable outside of a specific [set of] application(s); i.e. if for
example the rule should apply irrespective of that update being done via
an ad hoc SQL request, via a DFU request, etc., or via the [set of]
authorized application interface(s).
But I would expect the true desire is something different than
alluded; i.e. I doubt resetting what the program does is the desired
effect.? To ensure the effects are more accurate and representative of
the business [i.e. valid transactions], I would expect some other
approach like a CHECK CONSTRAINT that would fail the improper I/O would
be more appropriate.
If not, what would be the best method with the least impact on
performance?
The best method is to ensure an application is the only updater of
the data, and that the application would ensure that business rule is
applied. Often that would have all the programs invoking one service
program that owned the update interface to that data, at least to the
function of decrementing the inventory in that file, and that one
service program ensures the decrement below zero sets the value to zero
instead of the negative value.
If the value must be zero only in reading, then [if using SQL] an
expression such as the following, might be better for zero-impact on
updates, but obviously some impact on SELECT performance [and more if
the column is used for selection; a derived INDEX with the same
expression could help]:
CASE SIGN(Inventory_field)
WHEN 1 THEN Inventory_field
ELSE 0.0
END as Inventory_field
If so, what is the impact on performance?
The impact of the trigger in normal non-batch-style work is generally
insignificant, especially if the program is either in a named activation
group or the activation group of the *CALLER and the caller is not
generated *NEW nor the default activation, and the activation is not
reclaimed unnecessarily.
The system is at release V7R1M0 L00 TL12115
Refreshing to see that tidbit; so rare that such information ever is
offered in an OP describing an issue.
midrange.com
