Re: trigger advice needed

 David & John, 

        While I agree with David that the problem could be fixed with a 
design change, maybe there is another way. David, instead of trying to 
fix the associated records in the trigger program, could you not send a 
data queue entry to another process that would then roll through the PF 
with a SETLL/READE loop and reset the flags.  

        By the way, just to take John's concepts one step further, when 
the dunning letter goes out I would start a new transaction file that 
tracks the dunning status for historical review.

        Good Luck, 
        JMS


On Tue, 27 Oct 1998, John Carr wrote:

> 
> 
> 
> 
> > Here's the scenario with all the gory details:
> > MASTR_FILE is a file of parking tickets.  The agency responsible for 
> > "parking enforcement" is embarking on a campaign where parking
> > ticket scofflaws would receive Default Judgments for unpaid parking
> > tickets.  Before we can file a Default Judgment, we must send the
> > individual a Default Judgment Notice.  To "qualify" to receive a 
> > Default Judgment Notice, an individual  would have to have atleast 
> > 5 unpaid parking tickets.  Once a Default Judgment Notice is sent,
> > an individual has 30 days to pay the tickets or a Default Judgment 
> > is filed in the county clerk's office.  My problem occurs when someone 
> > with 5 unpaid tickets receives a Notice, and proceeds to pay off
> > 1 of the parking tickets.  When a tickets paid, it's status changes from
> >a 'N' to a '0'.
> >
> >    This individual would then have 4 unpaid tickets, so they would
> > not be eligible to receive a Default Judgment.  I then need to change
> > the status  of the other 4 tickets from 'N' to whatever it was before the
> > Default Judgment Notice was sent.>
> 
> David
> 
> As I suspected, Data base design.
> 
> If you had a Master File for the "Person", and Transactions for the tickets,
> it would be a better design.   You are looking at a "Cumulative" status
> based on multiple transaction States with the Default Notice thing.  If the 
> True master file for that person(As opposed to the ticket transactions)
> had a flag to indicate that person's "Default Notice" status,  leaving 
> each ticket transaction either paid, not paid, etc.  You could reset that
> one person's Master File flag upon receiving payment for the one 
> Ticket Transaction.  Each ticket transaction would then once again 
> become a "Single Fact Holder" in stead of "Multiple Fact Holder" that it
> is now(It's own status and the  "Cumultative Status" it participates in)
> 
> I know you probably do not have latitude to change the file design,  But
> I want to clearify the fact that in my years of IS,MIS,IT that the majority
> of the time if we have to do gymnastics in a program, it's usually because
> of the underlying data base design.  
> 
> 
> You can get indications that the design is wrong if you have to "Remember"
> what the previous statuses were and reset each transaction back to that
> value(""to whatever it was before the Default Judgment Notice was sent"") 
> when a "Cumulative" event happens to the real master file(the person entity).
> 
> The SQL example that was sent seemed neat.  
> BUT,  Don't mistake the fact that,  even though there is a "Trick" way 
> of getting out of the mess, that the reason for the "mess" in the first
> place, as nearly always, it data base design.  (Roll over Codd).
> 
> Just a thought
> 
> John Carr
> EdgeTech
> Have Classes,  Will Travel
> 
> 
> +---
> | This is the Midrange System Mailing List!
> | To submit a new message, send your mail to MIDRANGE-L@midrange.com.
> | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
> | To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator: david@midrange.com
> +---
> 
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---