MySQL Table Change Logging Trigger Generator Script

triggers.php

<?php

/**
 * MySQL Table Change Logging Trigger Generator Script
 *
 * This script generates triggers for all tables in the $dbname database,
 * which log INSERT, UPDATE, and DELETE operations.
 * The triggers record modifications in the 'change_log' table,
 * storing the name of the affected table, the type of change (INSERT, UPDATE, DELETE),
 * the timestamp of the change, and the old and new data of the affected rows in JSON format.
 *
 * Initially, the script creates the 'change_log' table if it does not exist.
 * It then retrieves the names of all tables in the database, excluding the 'change_log' table,
 * and generates triggers for each table to log the modifications.
 *
 * CAUTION: Verify the database connection details before running the script!
 * IMPORTANT: This script can significantly load the database server.
 * It is recommended for use only in development or testing environments,
 * and should be avoided in high-traffic, production environments due to potential performance issues.
 */

$dbname = '';
$host = '127.0.0.1';
$user = 'root';
$pass = 'PASSWORD';

$pdo = new PDO("mysql:dbname=$dbname;host=$host", $user, $pass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$createChangeLogTableSQL = '
    CREATE TABLE IF NOT EXISTS change_log (
        id INT AUTO_INCREMENT PRIMARY KEY,
        table_name VARCHAR(64),
        change_type VARCHAR(10),
        change_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        old_data JSON NULL,
        new_data JSON NULL,
        INDEX idx_table_name (table_name),
        INDEX idx_change_type (change_type),
        INDEX idx_change_timestamp (change_timestamp)
    );
';
$pdo->exec($createChangeLogTableSQL);

$tablesQuery = $pdo->prepare("SELECT table_name FROM information_schema.tables WHERE table_schema = '$dbname'");
$tablesQuery->execute();
$tables = $tablesQuery->fetchAll(PDO::FETCH_COLUMN);
$tables = array_filter($tables, function ($table) {
    return $table != 'change_log';
});

$fields = [];
foreach ($tables as $table) {
    $fieldsQuery = $pdo->prepare("SELECT column_name FROM information_schema.columns WHERE table_schema = '$dbname' AND table_name = :table");
    $fieldsQuery->bindParam(':table', $table);
    $fieldsQuery->execute();
    $fields[$table] = $fieldsQuery->fetchAll(PDO::FETCH_COLUMN);
}

function generateNewDataOldDataSQL($tableName, $triggerType, $fields): array
{
    $jsonPartsNew = [];
    $jsonPartsOld = [];
    foreach ($fields[$tableName] as $field) {
        if ($triggerType != 'DELETE') {
            $jsonPartsNew[] = "'$field', NEW.$field";
        }
        if ($triggerType != 'INSERT') {
            $jsonPartsOld[] = "'$field', OLD.$field";
        }
    }
    $newData = $triggerType == 'DELETE' ? 'NULL' : 'JSON_OBJECT(' . implode(', ', $jsonPartsNew) . ')';
    $oldData = $triggerType == 'INSERT' ? 'NULL' : 'JSON_OBJECT(' . implode(', ', $jsonPartsOld) . ')';
    return [$newData, $oldData];
}

$createTriggerSQLTemplates = [
    'INSERT' =>
        'CREATE TRIGGER %TRIGGERNAME AFTER INSERT ON %TABLE_NAME FOR EACH ROW ' .
        'INSERT INTO change_log (table_name, change_type, old_data, new_data) ' .
        "VALUES ('%TABLE_NAME', 'INSERT', NULL, %NEW_DATA_SQL);",
    'UPDATE' =>
        'CREATE TRIGGER %TRIGGERNAME AFTER UPDATE ON %TABLE_NAME FOR EACH ROW ' .
        'INSERT INTO change_log (table_name, change_type, old_data, new_data) ' .
        "VALUES ('%TABLE_NAME', 'UPDATE', %OLD_DATA_SQL, %NEW_DATA_SQL);",
    'DELETE' =>
        'CREATE TRIGGER %TRIGGERNAME AFTER DELETE ON %TABLE_NAME FOR EACH ROW ' .
        'INSERT INTO change_log (table_name, change_type, old_data, new_data) ' .
        "VALUES ('%TABLE_NAME', 'DELETE', %OLD_DATA_SQL, NULL);",
];

foreach ($tables as $table) {
    foreach ($createTriggerSQLTemplates as $type => $sqlTemplate) {
        $triggerName = sprintf('change_log_%s_%s', $table, strtolower($type));
        $checkTriggerSQL = "SHOW TRIGGERS WHERE `Trigger` LIKE '%$triggerName%'";
        $stmt = $pdo->prepare($checkTriggerSQL);
        $stmt->execute();
        if ($stmt->rowCount() > 0) {
            $dropTriggerSQL = "DROP TRIGGER $triggerName;";
            $pdo->exec($dropTriggerSQL);
        }
        list($jsonObjectNewDataSql, $jsonObjectOldDataSql) = generateNewDataOldDataSQL($table, $type, $fields);
        $createTriggerSql = str_replace(
            ['%TRIGGERNAME', '%TABLE_NAME', '%OLD_DATA_SQL', '%NEW_DATA_SQL'],
            [$triggerName, $table, $jsonObjectOldDataSql, $jsonObjectNewDataSql],
            $sqlTemplate
        );
        $pdo->exec($createTriggerSql);
    }
}

echo "Done.\n";