Automated Data Audit trail

[Bhavbhuti]

Hi all

Ages back, I was shared a beta version of FlameRobin that included a menu option to automatically create INSERT UPDATE DELETE triggers that would create an audit record in a special table for this purposes.

I have tried looking for that version but it has been lost somewhere else I would have shared it here. Is it possible to resurrect this feature?

Kind regards
Bhavbhuti

[arvanus]

Hello @Bhavbhuti
Don't you have this version anymore, correct?

Isn't it the "Create change Trigger for {TABLE_NAME}"?

If not, do you remember how was the audit table modeled?
Wast it one mirror audit table per table, with an additional columns like timestamp, user, role, operation (I,U, D)?
Or was it an unique table with the columns table_name, timestamp, user, role, operation (I,U, D), JSON, where the JSON field received all of the row`s data?
or else, if was an generic audit table with table_name, timestamp, user, role, operation (I,U, D), column1, column2, column3.... ?

Thanks!

[Jdochoa]

This characteristic is present in actual version.
But not is audit, is more like logger of the flamerobin operations.

For application

For data base

If the teaam will be implemented the audit characteristic i suggest use UDR for FB3+

[Bhavbhuti]

Thanks both. Sorry for replying late.

Thank ldochoa. I did not know about this option but yes from the sound of it it looks like it is logging operations not data modifications

Thanks arvanus. We had to right click each table (first time and then onwards everytime we modified the table structure). It would create/update triggers for INSERT, UPDATE, DELETE. These triggers would then compare OLD NEW of each of the fields and generate a record in one special table for audit within the same database.

Sadly, I am still looking for any thing I might have tested back then. I found example SQL scripts I had written on 12/Oct/2012, as below. So maybe we can explore the snapshots from October 2012 of FlameRobin. I have forgotten the name of the original developer for FlameRobin back in 2012, if he can be referred to

Kind regards
Bhavbhuti

SELECT a., f.
FROM audit_log a
JOIN audit_log_field f
ON f.id = a.ID
WHERE a.tabela = 'SSALEINVOICEITEM'
AND a.PK = 459
ORDER BY a.id DESC

SELECT a., f.
FROM audit_log a
JOIN audit_log_field f
ON f.id = a.ID
WHERE a.tabela = 'SSALEINVOICEFOOTER'
AND a.PK = 2323
AND f.kolona = 'BAMT'
ORDER BY a.id DESC

SELECT a., f.
FROM audit_log a
JOIN audit_log_field f
ON f.id = a.ID
WHERE a.tabela = 'SSALEINVOICEITEM'
AND a.PK IN (SELECT DISTINCT aa.pk
FROM audit_log aa
JOIN audit_log_field ff
ON ff.id = aa.id
WHERE aa.tabela = 'SSALEINVOICEITEM'
AND aa.operacija = 'DEL')
ORDER BY a.id DESC

[Jdochoa]

Hi @Bhavbhuti
FR provide one mechanism for this type of activities.
Your descriptions is like a code_templates using in FR.
I suggest this:

1. Define your tables.
2. In the folder code-templates (of FR) create the files:

• create_audit_trigger.confdef: this use for define GUI.
• create_audit_trigger.info: this agreggate a context menu.
• create_audit_trigger.template: This define the code to generate.

Use the files create_change_trigger.* to example

./jo

[Bhavbhuti]

Thanks Jo. I will have a look, not sure if I am capable of creating the template, but will surely have a look at the possibilities

Kind regards
Bhavbhuti