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

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.

This thread ...

Return to Archive home page | Return to MIDRANGE.COM home page