Dynamically constructing MySQL Code for creating a

Question

I am trying to implement audit trail/logging for a MySQL database. Now I have created an "audit" db (let's call it company_audit) which will contain "audit" copies of certain tables in the main db. (Say for company.customers I will create company_audit.customers. The audit table will contain the same exact columns as in the original table.

I want to use a trigger to insert changes in the original table into the audit table like so:

DELIMITER $$
DROP TRIGGER IF EXISTS customers_history_AU$$
CREATE TRIGGER customers_history_AU
AFTER UPDATE ON customers
FOR EACH ROW
BEGIN
  INSERT INTO company_audit.customers (customer_id, col2, col3, col4, col5)
  VALUES (OLD.customer_id, OLD.col2, OLD.col3, OLD.col4, OLD.col5);
END$$
DELIMITER ;

Now to my question: How can I construct the SQL string in the listing above (using say, a stored procedure) such that the column names can be obtained dynamically (say, from information_schema).

I have seen something similar here: http://uber-code.blogspot.com/2011/02/mysql-audit-logging-triggers.html, but the code here is not meant for retrieving column names from the original table, or perhaps I couldn't wrap my head around it so successfully!

Thanks.

Answer 1

Not having received any definite solution for this question, I have proceeded to cobble up a proof of concept option (since MySQL natively would not let you run SQL code that creates a trigger, using Prepared Statements). Please feel free to make any positive input.

DELIMITER //
DROP PROCEDURE IF EXISTS createAuditTable//
CREATE PROCEDURE createAuditTable(tblname CHAR(30), sufftxt CHAR(10), pri CHAR(20), filename CHAR(255) )
BEGIN
    SELECT DATABASE() INTO @dbname;
    SET @srctbl = CONCAT(@dbname, ".", tblname);
    SET @destdb = CONCAT(@dbname, "_", sufftxt);
    SET @desttbl = CONCAT(@destdb, ".", tblname);

    SET @str1 = CONCAT( "CREATE DATABASE IF NOT EXISTS ", @destdb);
    PREPARE stmt1 FROM @str1;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;

    SET @str2 = "SET FOREIGN_KEY_CHECKS=0";
    PREPARE stmt2 FROM @str2;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;

    SELECT COUNT(*) FROM information_schema.tables WHERE table_name = tblname AND table_schema = @destdb INTO @tblcount;
    IF (@tblcount = 0) THEN 
        SET @str3 = CONCAT("CREATE TABLE ", @desttbl, " LIKE ", @srctbl);
        PREPARE stmt3 FROM @str3;
        EXECUTE stmt3;
        DEALLOCATE PREPARE stmt3;
    END IF;

    SELECT COUNT(*) FROM information_schema.columns WHERE table_name = tblname AND table_schema = @destdb AND column_key = 'PRI' INTO @keycount;

    IF (@keycount <> 0) THEN 
        SET @str4 = CONCAT("ALTER TABLE ", @desttbl, " DROP PRIMARY KEY, ADD INDEX ", pri, " (", pri, ")" );
        PREPARE stmt4 FROM @str4;
        EXECUTE stmt4;
        DEALLOCATE PREPARE stmt4;
    END IF;

SELECT CONCAT( "DELIMITER $$
DROP TRIGGER IF EXISTS ", tblname, "_history_BU$$
CREATE TRIGGER ", tblname, "_history_BU
BEFORE UPDATE ON ", tblname, "
FOR EACH ROW
BEGIN
    INSERT INTO ", @desttbl, " (",
(SELECT GROUP_CONCAT(column_name) FROM information_schema.columns WHERE table_schema = @dbname AND table_name = tblname), ") ",
    "
    VALUES(", 
(SELECT GROUP_CONCAT('OLD.', column_name) FROM information_schema.columns WHERE table_schema = @dbname AND table_name = tblname),
 ");
END$$
DELIMITER ;"
 ) AS qstr FROM DUAL INTO @triggertxt;

SET @savestr = CONCAT('SELECT ', '"', @triggertxt, '"', " INTO DUMPFILE ", '"', filename, '"');
PREPARE stmt5 FROM @savestr;
EXECUTE stmt5;
DEALLOCATE PREPARE stmt5;


END//
DELIMITER ;  

TO USE, call the Procedure:

CALL createAuditTable('name_of_table', 'history', 'pri_key_fld', 'path/to/file.sql');

A new database is created using the name of your current working DB, with a suffix of "_history" appended to it. The table "name_of_table" is created in this new DB, identical to the original table The field "pri_key_fld" (which should be the primary/unique key of the table "name_of_table") is converted into an ordinary "INDEX" key. The purpose of this is to avert unique violations during audit logging of multiple rows in the future.

THEN Run the file created by the procedure: SOURCE 'path/to/file.sql'; (or any alternative syntax to run SQL from that file)

A couple of Caveats: Right now, you can only supply one field for "pri_key_fld". Ideally, we would want to supply an "array" containing all the unique fields in that table. Currently, if you have more than one unique field, unique violations will prevent you from logging more than one row. And that's not nice!

Again, it is obviously very clumsy and non-performant to go through the process of creating a file on disk, only to read SQL from the same file in the next command. One alternative one can explore to ameliorate is this: Run the CALL createAuditTable portion from the command line, catch the output as text, then Run the same as SQL right there on the command line. I did attempt that on Windows PowerShell; but the output was riddled with literal "\r\n" strings (representing line breaks). I didn't have the time to immediately work on cleaning out this string, so it's in the fridge now!

Finally, O ye MySQL ninjas, please be nice. I'm no pro, really. This is just a grow-your-own-grocery attempt at solving a practical problem.

Thank you.

Answer 2

This article describes how to implement a dynamic audit trigger in Sql Server, you might be able to port to MySQL.

https://www.simple-talk.com/sql/database-administration/pop-rivetts-sql-server-faq-no.5-pop-on-the-audit-trail/