You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Using trigger based stored procedure to create audit table. It follows the word press meta data approach to store the changes, so all the data is stores in just two centralized tables.
MySQL Component Requirements
I put the requirement here so in case you want to run this in a lower version of mysql, you'll know where to change.
v5.x Trigger support
v5.0.10 INFORMATION_SCHEMA.TRIGGERS
v5.0.32 DROP ... IF EXISTS
v5.6.1 Base64 (not yet implemented)
Usage
Run 'mysql_sp_audit_setup.sql' script.
This will create:
zaudit and zaudit_meta tables (the tables that hold the data for all the audits)
Stored procedures:
zsp_generate_audit: generates audit script for one table
zsp_generate_batch_audit: generates a script for multiple table at a time
zsp_generate_remove_audit: generates the script to remove audit from one table
zsp_generate_batch_remove_audit: generates a script for multiple table at a time
To Enable the audit on the table you want.
zsp_generate_audit( @audit_schema_name, @audit_table_name, OUT @script, OUT @errors )
CALL zsp_generate_audit( 'mydb_name', 'my_table_name', @output_script, @output_errors);
SELECT @output_script, @output_errors;
-- now, copy the output_script column value and save to sql-- once you run that newly create sql file, the table you specified -- above will audit all create/delete/update transactions
Copy the value from @output_script and run it
Now you should see three triggers and 2 new views on the contact table
Triggers: zcontact_AINS, zcontact_AUPD, and zcontact_ADEL
Views: zvw_audit_contact and zvw_audit_contact_meta
Features
Using stored procedures to generate the audit setup and remove scripts
The script will includes pre-generated views for easy access to the data
Centralized audit data, everything is stored in two table (similar to wordpress meta)
Allow the table's schemas to change, just need to rerun the stored procedure
* Keep deleted columns data
All values are stored as LONGTEXT therefore no blob support (as of now)
Allow audit table up to 2 primary keys
Stored Procedures
zsp_generate_audit( @audit_schema_name, @audit_table_name, OUT @script, OUT @errors )
* Generate the audit script for one table
zsp_generate_batch_audit ( @audit_schema_name, @audit_tables, OUT @script, OUT @errors )
* Put the comma separated list of table names to generate a batch of audit scripts
zsp_generate_remove_audit( @audit_schema_name, @audit_table_name, OUT @script )
* Generate the script to remove the triggers and views
zsp_generate_batch_remove_audit ( @audit_schema_name, @audit_tables, OUT @script)
* Put the comma separated list of table names to generate a batch script that remove all the tables specified
Conflict
Since MySQL 5 only allow one trigger per action, you have to merge your existing triggers with our audit trigger.
If you already have a trigger on your table, this is how you resolve it:
Copy the code for your trigger, then remove it
Run zsp_generate_audit()
Edit the trigger and add the code you copied to the appropriate trigger
Conventions
All names are prefixed with "z" to stay out of the way of your important stuff