MySQL报错#1336：触发器中禁止动态SQL，求解决方案（附代码）

Hey there, let's get this trigger issue sorted out for you! That #1336 error is telling you exactly what's wrong: MySQL does not allow dynamic SQL (like PREPARE/EXECUTE) inside triggers or stored functions. This is a deliberate design restriction to avoid security risks and execution context conflicts.

Let's Break Down Your Code #

Your current trigger tries to use a variable for the column name (TMPCOL = 'ID') and build a dynamic INSERT statement. But since dynamic SQL is off-limits here, we need to adjust the approach based on your actual needs:

Scenario 1: Your Column Name Is Fixed (e.g., Always ID) #

If you're always targeting the ID column (like your example shows), you don't need dynamic SQL at all. Just rewrite the trigger with a static INSERT statement:

BEGIN
    INSERT INTO TMP(DATA1, DATA2) VALUES ("DATA", OLD.ID);
END

This is the simplest fix—no dynamic code needed, and it avoids the error entirely.

Scenario 2: You Need Dynamic Column Names #

If your use case requires the column name to change dynamically (not just hardcoded to ID), you'll need to move the dynamic SQL logic into a stored procedure (since stored procedures do allow dynamic SQL), then call that procedure from your trigger.

Step 1: Create the Stored Procedure

DELIMITER //
CREATE PROCEDURE InsertIntoTmpWithDynamicCol(IN targetCol VARCHAR(100), IN rowId INT)
BEGIN
    -- Build the dynamic query (backticks around column name avoid syntax issues)
    SET @sql = CONCAT('INSERT INTO TMP(DATA1, DATA2) VALUES ("DATA", (SELECT `', targetCol, '` FROM your_source_table WHERE id = ?))');
    
    -- Pass row ID as a parameter to prevent SQL injection
    SET @row_id = rowId;
    
    -- Execute the dynamic query
    PREPARE stmt FROM @sql;
    EXECUTE stmt USING @row_id;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

(Replace your_source_table with the actual name of the table your trigger is attached to.)

Step 2: Update the Trigger to Call the Procedure

BEGIN
    -- Call the stored procedure with your target column and the row's ID
    CALL InsertIntoTmpWithDynamicCol('ID', OLD.id);
END

This way, the dynamic SQL runs in the stored procedure (where it's allowed), and the trigger just triggers the procedure call.

Key Takeaway #

MySQL's restriction on dynamic SQL in triggers is non-negotiable, but you can work around it by either:

• Using static SQL if your logic doesn't require dynamic elements, or
• Offloading dynamic logic to a stored procedure that your trigger calls.

内容的提问来源于stack exchange，提问作者Atul Baldaniya