0

I have been searching the forum for a good audit-logger, and found this super nice one: Record logging

First of all - it is a good audit log ! I really like it from an ADMIN perspective. Do you have a surgestion how I can transform the presentation of the audit log into a more user-friendly setup.

I have added the audit log trigger to a table tbl_setup_paycurve (paycurve_id, paycurve_name, kpi_id). Let's say I create a view (vw_paycurve) where I add the FK's value fx vw_paycurve.kpi_name.

Now somebody changes the paycurve_name. The audit log will log Table='tbl_setup_paycurve', FieldName='paycurve_name', PK='paycurve_id=1', OldValue='oldval', NewValue='Thisisanewvalue'.

HERE comes the question; How can I join the vw_paycurve and tbl_audit in a dynamic way? I want all the data in vw_paycurve and tbl_audit inner joined on the PK values.

Thanks!

exASHacto
  • 23
  • 1
  • 4
  • What have you tried? Just join on the Primary Key... Which part are you stuck at? – Milney Aug 18 '17 at 13:47
  • P.S. You should usually perform logging at the application layer... – Milney Aug 18 '17 at 13:49
  • You might have a look [at this answer](https://stackoverflow.com/a/37392690/5089204). The audit events are written as XML, which can be easily transformed into a presentable format. But any triggered approach will affect the performance... – Shnugo Aug 18 '17 at 14:28
  • Btw: I just re-worked this older answer to let it deal with `insert` and `delete` calls as well and made it applicable for `NULL` values. – Shnugo Aug 18 '17 at 15:56
  • The audit log will contain logs for multiple tables. I can't join on the PK since it is a text string a la 'paycurve_id=1'. @Shnugo I can see you have created a xml log string, I suppose you will have the same "issue" to present the log data in a more user-friendly way. – exASHacto Aug 21 '17 at 08:03
  • @exASHacto, The *presentation* is a question of the tool. This XML can be transfered to HTML via XSLT and shown in a browser in milliseconds... In the linked question the accepted answer is not a solution but rather a list of things one must keep in mind. Did you read this? There is a reason, why there's no *easy going*... – Shnugo Aug 21 '17 at 08:07
  • @Shnugo - you are right. I'm using MS Access to present the data - I know it is not the best solution... I think I need to log the whole record on UPDATE since the FK id's makes no sense for the user. – exASHacto Aug 21 '17 at 08:35

0 Answers0