如何在触发器中以JSON格式存储OLD对象并动态创建通用触发器

Great question! Let's break this down into two parts: converting the OLD row object to JSON for storage, and building a dynamic solution to create triggers for any table that logs to a central audit table.

First, let's refine the audit table structure to properly handle JSON data (your original tempdata table uses table which is a MySQL reserved keyword, so we'll adjust that and add useful metadata for better tracking):

CREATE TABLE IF NOT EXISTS `log`.`tempdata` (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    old_data JSON NOT NULL,
    event_type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

1. Converting OLD to JSON in Triggers #

You can't directly insert the OLD row object into a JSON column—you need to explicitly convert it using MySQL's JSON_OBJECT() function. For a fixed table like outlets, you could write a manual trigger like this:

DROP TRIGGER IF EXISTS trig_delete_test_outlet;
DELIMITER |
CREATE TRIGGER trig_delete_test_outlet AFTER DELETE ON outlets
FOR EACH ROW BEGIN
    INSERT INTO `log`.`tempdata`(table_name, old_data, event_type)
    VALUES(
        'outlets',
        JSON_OBJECT(
            'id', OLD.id,
            'name', OLD.name,
            'address', OLD.address -- Add all your table columns here
        ),
        'DELETE'
    );
END;
| DELIMITER ;

But this isn't scalable for multiple tables. That's where dynamic SQL and stored procedures come in to automate the process.

2. Dynamic Trigger Creation for Any Table #

We'll build a stored procedure that automatically generates triggers for any table, pulling column names dynamically from information_schema.columns to construct the JSON object without hardcoding columns.

Step 1: Create the Dynamic Trigger Procedure #

DELIMITER //

CREATE PROCEDURE CreateAuditTrigger(
    IN target_table VARCHAR(100),
    IN trigger_event ENUM('INSERT', 'UPDATE', 'DELETE')
)
BEGIN
    DECLARE col_list TEXT;
    DECLARE trigger_name VARCHAR(150);
    DECLARE sql_stmt TEXT;

    -- Fetch all columns for the target table to build the JSON_OBJECT string
    SELECT GROUP_CONCAT(
        CONCAT('`', column_name, '`, OLD.`', column_name, '`')
    ) INTO col_list
    FROM information_schema.columns
    WHERE table_schema = DATABASE() -- Uses current DB; adjust if your tables are in another schema
      AND table_name = target_table;

    -- Generate a unique, descriptive trigger name
    SET trigger_name = CONCAT('trig_', trigger_event, '_', target_table);

    -- Build the trigger SQL based on the event type
    IF trigger_event = 'DELETE' OR trigger_event = 'UPDATE' THEN
        SET sql_stmt = CONCAT(
            'DROP TRIGGER IF EXISTS ', trigger_name, '; ',
            'CREATE TRIGGER ', trigger_name, ' AFTER ', trigger_event, ' ON ', target_table, ' ',
            'FOR EACH ROW BEGIN ',
            'INSERT INTO `log`.`tempdata`(table_name, old_data, event_type) ',
            'VALUES(', QUOTE(target_table), ', JSON_OBJECT(', col_list, '), ', QUOTE(trigger_event), '); ',
            'END;'
        );
    ELSEIF trigger_event = 'INSERT' THEN
        -- For INSERT events, use NEW instead of OLD since there's no existing row to reference
        SELECT GROUP_CONCAT(
            CONCAT('`', column_name, '`, NEW.`', column_name, '`')
        ) INTO col_list
        FROM information_schema.columns
        WHERE table_schema = DATABASE()
          AND table_name = target_table;

        SET sql_stmt = CONCAT(
            'DROP TRIGGER IF EXISTS ', trigger_name, '; ',
            'CREATE TRIGGER ', trigger_name, ' AFTER ', trigger_event, ' ON ', target_table, ' ',
            'FOR EACH ROW BEGIN ',
            'INSERT INTO `log`.`tempdata`(table_name, old_data, event_type) ',
            'VALUES(', QUOTE(target_table), ', JSON_OBJECT(', col_list, '), ', QUOTE(trigger_event), '); ',
            'END;'
        );
    END IF;

    -- Execute the dynamic SQL to create the trigger
    PREPARE stmt FROM sql_stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

Step 2: Use the Procedure to Create Triggers #

Now you can generate triggers for any table with a single, simple call:

-- Create a DELETE trigger for the `outlets` table
CALL CreateAuditTrigger('outlets', 'DELETE');

-- Create an UPDATE trigger for a `users` table
CALL CreateAuditTrigger('users', 'UPDATE');

-- Create an INSERT trigger for a `products` table
CALL CreateAuditTrigger('products', 'INSERT');

Key Notes #

• MySQL Version: This solution requires MySQL 5.7 or later (when native JSON support was introduced).
• Permissions: The user running the stored procedure needs TRIGGER permission on the target tables, and INSERT permission on log.tempdata.
• Reserved Words: Always avoid using MySQL reserved keywords (like table) as column names—we renamed it to table_name to prevent errors.
• NULL Handling: JSON_OBJECT() automatically includes NULL values from the row, so you don't have to worry about missing or incomplete data in your logs.

内容的提问来源于stack exchange，提问作者Bhupendra Pandey