BEFORE UPDATE触发器无法正确获取NEW.value值的问题求助

问题描述 #

编写BEFORE UPDATE触发器，用于在更新phone_client_item_1表前向object_param_value_text表插入数据时，遇到两个异常：

• 直接使用NEW.id或NEW.object_id时值为空
• 将NEW.object_id赋值给局部变量后，生成的INSERT语句中出现NAME_CONST('new_object_id',14)形式，而非实际数值14

调试日志 #

调试日志中可正常显示NEW字段的正确值：

<------><------>    22 Query<-->SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT =  NAME_CONST('debug_message',_utf8mb3'Debug: NEW.object_id=14, NEW.id=5024, NEW.date1=2021-02-08, NEW.type=1, NEW.cid=6968' COLLATE 'utf8mb3<------><------>    22 Query<-->commit

当前触发器（TRIGGER №1）代码 #

BEGIN
  DECLARE new_object_id INT;
  SET new_object_id = NEW.object_id;
  GET DIAGNOSTICS new_object_id = NUMBER;
  -- Now you can use new_object_id directly in your trigger logic
  -- For example, you can use it in the INSERT statement
  IF new_object_id != 999 THEN
    INSERT INTO object_param_value_text (object_id, param_id, `value`) VALUES (new_object_id, 1, '123');
  END IF;
END

表结构 #

phone_client_item_1表 #

CREATE TABLE `phone_client_item_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cid` int(11) NOT NULL DEFAULT 0,
  `object_id` int(11) NOT NULL,
  `type` tinyint(4) NOT NULL DEFAULT 0,
  `source_id` int(11) NOT NULL DEFAULT 0,
  `date1` date DEFAULT NULL,
  `date2` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `date1` (`date1`),
  KEY `date2` (`date2`),
  KEY `cid` (`cid`),
  KEY `object_id` (`object_id`),
  KEY `source_id` (`source_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5689 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

object_param_value_text表 #

CREATE TABLE `object_param_value_text` (
  `object_id` int(11) NOT NULL DEFAULT 0,
  `param_id` int(11) NOT NULL DEFAULT 0,
  `value` varchar(250) NOT NULL DEFAULT '',
  PRIMARY KEY (`param_id`,`object_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

UPDATE语句及触发器生成的SQL #

<------><------>    42 Prepare<>UPDATE phone_client_item_1 SET cid=?, type=?, source_id=?, date1=?, date2=?,  comment=?, object_id=?, alias=? WHERE id=?
<------><------>    42 Execute<>UPDATE phone_client_item_1 SET cid=6968, type=1, source_id=7, date1=DATE'2021-01-13', date2=NULL,  comment='[LOCAL] ???? ?????', object_id=14, alias='' WHERE id=4990
<------><------>    42 Query<-->INSERT INTO object_param_value_text (object_id, param_id, `value`) VALUES ( NAME_CONST('new_object_id',14), 1, '123')

正常工作的触发器（TRIGGER №2）代码 #

BEGIN

    -- Get the last inserted ID from the object table
    SELECT `AUTO_INCREMENT` INTO @object_id
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'bgbilling'
    AND TABLE_NAME = 'object';

    -- Modify the values before insert
    SET NEW.date1 = NOW();
    
    -- Use LPAD within CONCAT to format object_id with leading zeros directly
    IF NEW.type_id = 1 THEN
        INSERT INTO object_param_value_text (object_id, param_id, `value`) 
            VALUES (@object_id, 3, CONCAT('sp_', NEW.cid, '-', LPAD(@object_id, 5, '0')));
        INSERT INTO object_param_value_text (object_id, param_id, `value`) 
            VALUES (@object_id, 2, CAST(SUBSTRING(MD5(RAND()), 1, 12) AS CHAR));
        INSERT INTO object_param_value_list (object_id, param_id, `value`) 
            VALUES (@object_id, 10, 16);
        INSERT INTO object_param_value_list (object_id, param_id, `value`) 
            VALUES (@object_id, 11, 20);
        INSERT INTO object_param_value_list (object_id, param_id, `value`) 
            VALUES (@object_id, 12, 21);
        INSERT INTO object_param_value_flag (object_id, param_id, `value`) 
            VALUES (@object_id, 13, 0);
    END IF;
END

尝试过的操作 #

• 切换过BEFORE UPDATE和AFTER UPDATE两种触发时机
• 直接使用NEW.date1时，生成的SQL中NEW.date1未被解析为实际值：

<------><------>    42 Close stmt
<------><------>    42 Prepare<>UPDATE phone_client_item_1 SET cid=?, type=?, source_id=?, date1=?, date2=?,  comment=?, object_id=?, alias=? WHERE id=?
<------><------>    42 Execute<>UPDATE phone_client_item_1 SET cid=6968, type=1, source_id=7, date1=DATE'2021-01-13', date2=NULL,  comment='[LOCAL] ???? ?????', object_id=14, alias='' WHERE id=499
<------><------>    42 Query<-->INSERT INTO object_param_value_text (object_id, param_id, `value`) VALUES ( NAME_CONST('new_object_id',14), 1, NEW.date1) 

期望结果 #

触发器生成类似以下的SQL语句，直接使用NEW字段的实际整数值：

INSERT INTO object_param_value_text (object_id, param_id, value) VALUES (14, 1, '123');

解决方案 #

1. 移除错误的诊断语句 #

当前触发器中的GET DIAGNOSTICS new_object_id = NUMBER;语句完全多余，它会覆盖你之前赋值的NEW.object_id值，导致异常行为，直接删除这行代码。

2. 直接使用NEW字段而非局部变量 #

修改触发器代码，直接引用NEW.object_id，避免使用局部变量，这样MySQL会直接使用实际数值生成INSERT语句：

BEGIN
  IF NEW.object_id != 999 THEN
    INSERT INTO object_param_value_text (object_id, param_id, `value`) 
    VALUES (NEW.object_id, 1, '123');
  END IF;
END

3. 若需使用变量，改用用户变量 #

如果必须使用变量存储NEW.object_id，使用用户变量（以@开头）替代局部变量，这样日志中会显示实际数值而非NAME_CONST语法：

BEGIN
  SET @new_object_id = NEW.object_id;
  IF @new_object_id != 999 THEN
    INSERT INTO object_param_value_text (object_id, param_id, `value`) 
    VALUES (@new_object_id, 1, '123');
  END IF;
END

说明 #

• NAME_CONST是MySQL内部用于传递常量的语法，实际执行时会正确替换为对应数值，但会导致日志显示不直观，改用用户变量或直接使用NEW字段可解决该问题。
• BEFORE UPDATE触发器中，NEW对象可正常访问所有字段的值，无论该字段是否在UPDATE语句中被修改，直接引用即可获取正确值。

内容的提问来源于stack exchange，提问作者Andrey Kostarev