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 <
mbsoftwaresoluti...@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]
_______________________________________________
Post Messages to: ProFox@leafe.com
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/cajgvlx2wdzczgjx-wxjeunrcebgzyuwqqlo0fjvbudoapvf...@mail.gmail.com
** 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.
