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 ccbibleman@gmail.com 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 < mbsoftwaresolutions@mbsoftwaresolutions.com> 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]