Audit Trail & MySQL

[Josh Ormond]

Looking to pick your brains to see about options to Log Changes for a db where the data resides in on MySQL server.

 

Connection is fine, db is working fine. But I don't know how to go about capturing changes made to the data for an audit trail. Before we decided to go with MySQL to host the website we are delevoping, I was using SuperLog and Audit Log. But with the odbc data soure the Get(ActiveFieldName) function doesn't work.

 

Any ideas about another way to generate an audit log?

[Josh Ormond]

After further thought. I decided to go with a scripted log.

 

While the Get (ActiveFieldName) function doesn't work on an external data source (and Audit Trail and SuperLog made it so easy), like MySQL and such, the zippscript and EventScript plugins still give the ability to trigger a script upon exiting a field.

 

With a little forethought I should be able to write one script and just add the Field parameters to each field. A little more work than native tables, but a necessary function for this solution.

[sourcebooks]

After further thought. I decided to go with a scripted log.

 

While the Get (ActiveFieldName) function doesn't work on an external data source (and Audit Trail and SuperLog made it so easy), like MySQL and such, the zippscript and EventScript plugins still give the ability to trigger a script upon exiting a field.

 

With a little forethought I should be able to write one script and just add the Field parameters to each field. A little more work than native tables, but a necessary function for this solution.

 

You can also create a trigger on the MySQL database to insert into an audit table that will store the values pre and post change.

[Josh Ormond]

Can you direct me to more info regarding the technique?

 

Everything I have done thus far in MySQL has just been setting up the tables to access through FileMaker. I haven't gotten into features and techniques on the MySQL side.

[sourcebooks]

Can you direct me to more info regarding the technique?

 

Everything I have done thus far in MySQL has just been setting up the tables to access through FileMaker. I haven't gotten into features and techniques on the MySQL side.

 

Here is the mysql documentation on it:

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

 

I would probably use a before update trigger that will insert into your audit table. Your able to capture the old values and new values when using a trigger.

 

create trigger `dbname`.`triggername` BEFORE UPDATE on `dbname`.`tablename`

for each row BEGIN

insert into audit_table (select old.field,old.field2 from changed_table);

insert into audit_table (select new.field,new.field2 from changed_table);

--Enter as many queries as you want

END;

 

 

As a side note Triggers are only available in MySQL 5+.

[Josh Ormond]

Awesome. Thanks for the tip sourcebooks.

 

I was only through Chapter 7 so far.

 

While some are critical of the FMP and its sql capabilities, I have to say for us, it has been a pleasent experience. It required a small change in the way we do a few things, but I try to be open to new ideas.