table 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
table 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
table changelog
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
trigger definition on table entries
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
the trigger works so far as is inserts data in the change_log table, but instead of the "real" node names it only inserts the id's.
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)
this query on the nodes table will get the name by id.
MariaDB [aix_registry_dev]> select name from nodes where id = 6728;
+----------------------+
| name |
+----------------------+
| KUG01115_WSAP_HA_LPM |
+----------------------+
1 row in set (0.000 sec)
how to integrate this subquery into the trigger to get the node names instead of the id's?
i already tried DECLARE and SET variations but this rendered the trigger dysfunctional.
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
Modify the trigger to get name from nodes rather than using NEW.node_id into the insert statement.
Try
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',
( SELECT name FROM nodes where id = NEW.node_id),
NEW.attrib_id,
NULL,
NEW.value,
'CREATE',
NOW()
);
END