Another option for generating a robust trigger code Including an optional AuditTrail) would be AMRI, which is a replacement for VFP's original RI builder. This was Freeware from Markus Auer (hence the "A M" in the name). If you can't find it at the usual tools libraries, send me a PM. wOOdy
-----Ursprüngliche Nachricht----- Von: ProFox <[email protected]> Im Auftrag von Eric Selje Gesendet: Donnerstag, 3. Oktober 2019 22:46 An: ProFox Email List <[email protected]> Betreff: Re: VFP9SP2 DBC -- update trigger I'd get FoxAudit (if you can find it) for this. Well worth the price and takes minutes to set up to get very very robust change management. Eric On Wed, Oct 2, 2019 at 6:33 AM Charlie Coleman <[email protected]> wrote: > Yes, with VFP it is that simple. > > I would suggest adding a timestamp to your logging table. Technically, > since your just doing inserts you could assume sequence - but seeing > the time of day of events might help track this down. That means your > log table structure would be slightly different of course (the extra > field). And if you're adding an extra field, you may as well add one > more that will show which trigger fired (add, update, or delete). > > Also, the way I've done this is create a stored procedure (aka > function) in the database itself. E.g. Modify Database, right-click, > choose Edit Stored Procedures. So, for example your procedure above > might be created like > -------------- > PROCEDURE logmydata(trigg_type) > > LOCAL oRec as Object > SCATTER MEMO NAME oRec > ..... (etc, your code above - maybe adding a datetime data value as > well) > > END PROC > --------------- > > Then in the table properties, you set the triggers.... > - Add Trigger: logmydata('a') > - Update Trigger: logmydata('u') > - Delete Trigger: logmydata(d') > > Or something like that. > > Here is one stored procedure I used that could handle logging data > across any table. In my case, I made the design such that 'log tables' > would start with "z0_" and then the table name (this was nice for > sorting purposes reporting on the database schema). And I had extra > fields: zlog_user, zlog_type, zlog_time in my log tables. I could have > added more 'error checking' but this worked fantastic in production > with never a problem. You will also see references to some 'global' > variables - aka oApp is the application object where I set user login > info, and I had a global 'lLogSuspend' to turn off logging if I wanted > to (I always thought I'd take that out of production, but it never > turned out to be an issue). I think this was back to VFP 6 or maybe > even earlier. I don't think the INSERT FROM NAME.... was available > yet. But you can see how it would be simple to modify - and I would definitely use objects (FROM NAME) if I rewrote it. > > ---------- > PROCEDURE fn_LOGCHANGE(_pType, cTbl) > LOCAL cLogTbl, tCurrTime, zlog_modtype, zlog_time, zlog_user, > table_ref, alt_id, lContinue > m.lContinue = .F. > IF TYPE("m.lLogSuspend") <> "U" > IF m.lLogSuspend == .F. > m.lContinue = .T. > ENDIF > ENDIF > > IF m.lContinue == .T. > m.cLogTbl = 'z0_' + m.cTbl > *-- using memvars so that it's easier to GATHER below - setting > the m.<varname> junk > SCATTER MEMO MEMVAR > IF TYPE("oApp.cUserID") = 'C' > *-- add the entries for the log_mod_type, log_time, log_user > m.zlog_modtype = m._pType > m.zlog_time = DATETIME() > m.zlog_user = oApp.cUserID > INSERT INTO (m.cLogTbl) FROM MEMVAR > ENDIF > ENDIF > ENDPROC > ------------------ > > Then in any table trigger, I could do: > fn_logchange('a', 'account_master') > fn_logchange('u', 'account_master') > fn_logchange('a', 'user_data') > fn_logchange('d', 'activity_daily') > fn_logchange('a', 'office_location') > > and so on... > > > HTH, > -Charlie > > On Wed, Oct 2, 2019 at 12:29 AM MB Software Solutions, LLC < > [email protected]> wrote: > > > I've got a client with a wonky situation where the data (for some > > unknown reason) gets "blanked out" (not deleted, mind you...just > > some fields set to blanks). I wanted to add an UPDATE trigger so > > that I could get some sort of historical update accounting to help > > me hone in on where the problem might be. > > > > If I've used triggers in VFP then I've totally forgotten how to use > > them. (Keep in mind that I haven't used VFP DBFs since 2004! This > > is an app I took over for one of our former buddies, Jeff Johnson, > > after he passed away.) > > > > I use triggers in MySQL/MariaDB all the time. There, I've got the > > record object in the OLD and NEW object keywords. Looking at the > > CREATE TRIGGER Command in VFP help makes me think that the table is > > the currently selected work area, and as such, I could just do a > > SCATTER MEMO NAME oRec and then use that oRec variable to insert > > values into a separate table. > > > > Wanted to bounce this off the community prior to my testing that theory > > tomorrow. Does that sound right? Tips for a better way to do it? I > > was going to create a "shadow" copy table and just basically do the > > following in a database stored procedure: > > > > * assuming current work area is the table where the UPDATE trigger > > is firing LOCAL oRec as Object SCATTER MEMO NAME oRec INSERT INTO > > MyTable_COPY FROM NAME oRec > > > > IF isblank(oRec.Field1) and isblank(oRec.Field2) and > > isblank(oRec.Field3) then && alert MBSS of problem scenario that > > should never happen but this must be the bug happening!!!! > > DO EmailMBSS > > ENDIF > > > > > > Can it really be that easy? > > > > tia, > > --Mike > > > > > > > > --- > > This email has been checked for viruses by Avast antivirus software. > > https://www.avast.com/antivirus > > > > [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: https://leafe.com/archives This message: https://leafe.com/archives/byMID/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
