This script is based in the code published in the bolg A little noise http://thenoyes.com/littlenoise/?p=43, it adds some functionality:
- Adds triggers for insert and delete writing all the values inserted / deleted to the auditlog table
- It takes the primary key from information schema instead of grabbing it as a parameter
- Stores the primary key as a varchar with the description (pkdesk saves the fields, and pkvalue the value of the primary key)
- Added the mysql username
- The tablename works with a like clause so if you send ‘%’ as table name you will get the script for all the tables in the current database.
Considerations:
- If the script file already exists you will get an error
- Tables that doesn’t have a primary key won’t script out.
- If you change something in the script, don’t forget to comment out the table drop/creation part so you wont loose your previous data.
Hope this come handy for everyone, it took me some time to write it down, so I would like to share it for everyone.
Finally, the script: Download
“The table engine should be InnoDB, because is the only one that accept triggers”
Since when?
Sorry, I was confused with foreing keys, I fix the article, thanks
I’ll try to put this to good use immediatley.
Frnalky I think that’s absolutely good stuff.
Can you please give an example as to how to use this. Somehow not able to use it.
Thanks.
Hi, just run the script, then call the stored procuedure
addLogTrigger with two parameters:
1st the table you wan to add the audit trail to (‘%’ for all tables)
2nd the script file name (Null if you want the result on the screen)
This will output another script that you have to run to get the audit trail working. Take into account that if you change a table structure you will need to run the stored procedure again for that specific table.
Hope it helps…
Javier
Thanks. I was able to get this working. One thing though. When I ran the procedure, it gave a warning.
+——-+——+————————–+
| Level | Code | Message |
+——-+——+————————–+
| Note | 1051 | Unknown table ‘temTable’ |
+——-+——+————————–+
After I ran the generated trigger, it did ran the 13 queries but gave warning for 3 of them. Not sure if it should be of any concern. I could not get the warning messages.
This is a very useful script. Thanks for sharing this.
That’s way more cvleer than I was expecting. Thanks!
e8cXag IJWTS wow! Why can’t I think of thngis like that?
Wow! That’s a relaly neat answer!
Way to go on this essay, hpeeld a ton.
Thanks alot pal.
Youve saved may day
What an awesome way to explain this?now I know eevrthying!
useful job for bringing something new to the internet!
ome genuinely nice stuff on this website , I enjoy it.
I stumbled on your website by accident while searching Yahoo and I’m glad I did. Your post is a bit different and I enjoyed reading it. I will in turn make a comment about it on my blog and point my visitors your way. Thanks.
This is very helpful, thanks for revealing. I will be confident others will view items this similar style
Thankyou for sharing Audit Trail in Mysql | JEL Soluciones Informáticas with us keep update bro love your article about Audit Trail in Mysql | JEL Soluciones Informáticas .
Thanks for the interesting information! I would really like to read more of this! Keep up the good writing and I will come back soon! Thanks