entradas de tabela
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
nós de tabela
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
registro de alterações da tabela
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
definição de gatilho em entradas de tabela
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
o gatilho funciona na medida em que insere dados na tabela change_log, mas em vez dos nomes de nós "reais", ele insere apenas os IDs.
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)
esta consulta na tabela de nós obterá o nome por id.
MariaDB [aix_registry_dev]> select name from nodes where id = 6728;
+----------------------+
| name |
+----------------------+
| KUG01115_WSAP_HA_LPM |
+----------------------+
1 row in set (0.000 sec)
como integrar esta subconsulta ao gatilho para obter os nomes dos nós em vez dos IDs?
eu já tentei variações DECLARE e SET, mas isso tornou o gatilho disfuncional.
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