如何在MariaDB触发器中关联表获取节点名称而非ID

背景说明 #

在MariaDB环境中，存在以下三张数据表：

表entries #

CREATE TABLE `entries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `node_id` int(11) NOT NULL,
  `attrib_id` int(11) NOT NULL,
  `value` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `unique_id` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `ENTRIES_UNIQUE_COMPOUND` (`unique_id`) USING BTREE,
  KEY `id` (`id`),
  KEY `HAS_A_ATTRIBUTE` (`attrib_id`),
  KEY `HAS_A_NODE` (`node_id`),
  CONSTRAINT `FK_ATTRIBUTE_ID` FOREIGN KEY (`attrib_id`) REFERENCES `attribs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_NODE_ID` FOREIGN KEY (`node_id`) REFERENCES `nodes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5547 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

表nodes #

CREATE TABLE `nodes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `last_seen` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `ts_nodes` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `nodes_unique_idx` (`name`,`ts_nodes`),
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6734 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

表change_log #

CREATE TABLE `change_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `node_name` varchar(256) COLLATE utf8_unicode_ci DEFAULT NULL,
  `attribute_name` varchar(256) COLLATE utf8_unicode_ci DEFAULT NULL,
  `old_value` varchar(256) COLLATE utf8_unicode_ci DEFAULT NULL,
  `new_value` varchar(256) COLLATE utf8_unicode_ci DEFAULT NULL,
  `action` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `ts` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `id` (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=603 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

当前触发器问题 #

已为entries表创建AFTER INSERT触发器：

CREATE TRIGGER `insert_entry_trigger` AFTER INSERT ON `entries`
 FOR EACH ROW BEGIN
       
             INSERT INTO change_log(type, node_name, attribute_name, old_value, new_value, action, ts)

             VALUES('ENTRIES', NEW.node_id, NEW.attrib_id, NULL, NEW.value, 'CREATE', NOW());

           END

该触发器可正常向change_log表插入数据，但node_name字段仅插入node_id数值，而非nodes表中对应的真实节点名称。查询change_log表数据示例：

MariaDB [aix_registry_dev]> select * from change_log LIMIT 10;
+-----+---------+-----------+----------------+-----------+-----------+--------+---------------------+
| id  | type    | node_name | attribute_name | old_value | new_value | action | ts                  |
+-----+---------+-----------+----------------+-----------+-----------+--------+---------------------+
| 271 | ENTRIES | 6728      | 1085           | AIX       | NULL      | DELETE | 2024-06-04 13:21:13 |
| 272 | ENTRIES | 6728      | 1086           | 0         | NULL      | DELETE | 2024-06-04 13:21:13 |
| 273 | ENTRIES | 6728      | 1087           | 0         | NULL      | DELETE | 2024-06-04 13:21:13 |
| 274 | ENTRIES | 6728      | 1088           | 1         | NULL      | DELETE | 2024-06-04 13:21:13 |
| 275 | ENTRIES | 6728      | 1089           | 0         | NULL      | DELETE | 2024-06-04 13:21:13 |
| 276 | ENTRIES | 6728      | 1090           | 1         | NULL      | DELETE | 2024-06-04 13:21:13 |
| 277 | ENTRIES | 6728      | 1091           | 0         | NULL      | DELETE | 2024-06-04 13:21:13 |
| 278 | ENTRIES | 6728      | 1092           | 1         | NULL      | DELETE | 2024-06-04 13:21:13 |
| 279 | ENTRIES | 6728      | 1093           | 0         | NULL      | DELETE | 2024-06-04 13:21:13 |
| 280 | ENTRIES | 6728      | 1094           | 0         | NULL      | DELETE | 2024-06-04 13:21:13 |
+-----+---------+-----------+----------------+-----------+-----------+--------+---------------------+
10 rows in set (0.000 sec)

通过以下查询可从nodes表根据ID获取对应节点名称：

MariaDB [aix_registry_dev]> select name  from nodes where id = 6728;
+----------------------+
| name                 |
+----------------------+
| KUG01115_WSAP_HA_LPM |
+----------------------+
1 row in set (0.000 sec)

问题需求 #

需修改触发器，使change_log表的node_name字段插入真实节点名称而非ID。用户尝试的修改导致触发器失效：

CREATE TRIGGER `insert_entry_trigger` AFTER INSERT ON `entries`
 FOR EACH ROW BEGIN
 DECLARE nodename varchar(256)
 SET nodename = (select name  from nodes where id = NEW.node_id)
       
             INSERT INTO change_log(type, node_name, attribute_name, old_value, new_value, action, ts)

             VALUES('ENTRIES', nodename, NEW.attrib_id, NULL, NEW.value, 'CREATE', NOW());

           END

解决方案 #

用户尝试的触发器失效原因是语法错误：DECLARE和SET语句末尾缺少分号。另外，也可以直接用INSERT...SELECT语句简化逻辑，无需声明变量：

方案1：修正变量声明语法 #

CREATE TRIGGER `insert_entry_trigger` AFTER INSERT ON `entries`
FOR EACH ROW BEGIN
    DECLARE nodename varchar(256); -- 末尾补充分号
    SET nodename = (SELECT name FROM nodes WHERE id = NEW.node_id); -- 末尾补充分号
    
    INSERT INTO change_log(type, node_name, attribute_name, old_value, new_value, action, ts)
    VALUES('ENTRIES', nodename, NEW.attrib_id, NULL, NEW.value, 'CREATE', NOW());
END

方案2：使用INSERT...SELECT简化（推荐） #

直接关联nodes表查询节点名称，一步完成插入：

CREATE TRIGGER `insert_entry_trigger` AFTER INSERT ON `entries`
FOR EACH ROW
    INSERT INTO change_log(type, node_name, attribute_name, old_value, new_value, action, ts)
    SELECT 
        'ENTRIES', 
        n.name, 
        NEW.attrib_id, 
        NULL, 
        NEW.value, 
        'CREATE', 
        NOW()
    FROM nodes n
    WHERE n.id = NEW.node_id;

注意事项 #

1. 由于entries表的node_id外键关联nodes表的id，所以NEW.node_id对应的记录一定存在于nodes表，无需处理空值情况。
2. 如果需要同时将attribute_name字段替换为真实属性名称，可采用相同方法关联attribs表查询。

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