On 10/17/2017 5:58 AM, Dave Crozier wrote: ...
I decided to add an audit trail feature into a small system here at Flexipol via the DBC Triggers roughly based on the example in MegaFox chapter 7.
In order to make things simple and more in line with OOP philosophy I decided to construct an Audit Class with all the logic embedded into a packaged class for adding into the audit tables. So far so good… However I now find that I cannot action the “add audit record" method in the class i.e.
oAudit.Log(”Add”, “”Customer”)
...
My only alternative is to go back to make all the auditing code .prg based which is fine but not an ideal solution.
As I recall, the "database environment" can access the environment it was run within. So, for example, if you opened the database from an "application" all the "public" objects of the application are visible to the database code (such as triggers and stored procedures). But if you have to allow for the database to be opened via ODBC or OLEDB, then pretty much only the environment of the database is in scope.
So, you could take your auditing code and create stored procedures out of it and it should be available all the time. But there are some restrictions in a "database only" environment (I don't recall them at the moment - maybe "creating objects" was something that could not be done in database-only code - I'm pretty sure you can do SCATTER/GATHER though - may have to use the MEMVAR clause).
As a quick example, this is a generic log stored proc I had (it is a stored proc in a VFP database). No vouching for it: I don't know for sure that I tested this through ODBC, etc. Basically, I would call this in the trigger code (INSERT, DELETE, MODIFY) and would pass 2 parameters. E.g. in the Insert trigger I would put: "fn_LOGdata('a', <name of table getting the record inserted>). Of course there was some set up before this - aka the log tables exist in the DB, they have an identical structure to the table being logged plus a few other fields. So maybe this will give you some ideas.
PROCEDURE fn_LOGdata(_pType, cTbl) *-- _pType: type of logging (a-add, d-delete, m-modify) *-- ctbl: the table being logged. The assumption is a log table of the exact same name, but with "z0_" as the 4th, 5th, and 6th characters, exists in the database
*-- if a global variable glnosprun exists, ALL stored procedure code is prevented from executing IF TYPE('m.glnosprun') == "U" LOCAL cLogTbl, tCurrTime, zlog_modtype, zlog_time, zlog_user, table_ref, ctmp m.tCurrTime = DATETIME() m.cLogTbl = "z0_" + ALLTRIM(m.cTbl) SELECT (m.cTbl) SCATTER MEMO MEMVAR
*-- set up values for the log_mod_type, log_time, log_user m.zlog_modtype = _pType m.zlog_time = tCurrTime *-- there may be a better way to get the logged on user... cTmp = SYS(0) m.zlog_user = IIF(ATC('#', cTmp) > 0, UPPER(ALLTRIM(SUBSTR(cTmp, ATC('#', cTmp) + 1))), 'NoNetwork') INSERT INTO (m.cLogTbl) FROM MEMVAR SELECT (m.cTbl) ELSE ENDIF ENDPROC
HTH, -Charlie