I need to create a trigger that, any time a goat's sireRegNo column is updated, its sireName column is automatically updated to match.
I have a database that stores animal genealogy. Each row stores both the names and the registration #s of both its parents. The sireName field is readonly, but registrars may change sireRegno.
CREATE TABLE `table_name` ( `id_goat` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(80) NOT NULL, `regNo` varchar(50) DEFAULT '', `sireName` varchar(80) DEFAULT NULL, `sireRegNo` varchar(50) DEFAULT '', PRIMARY KEY (`id_goat`,`name`), UNIQUE KEY `id_goat_UNIQUE` (`id_goat`), UNIQUE KEY `name_UNIQUE` (`name`), UNIQUE KEY `regNo_UNIQUE` (`regNo`) ) ENGINE=InnoDB AUTO_INCREMENT=188784 DEFAULT CHARSET=utf8;
table_name name | regNo | sireName | sireRegNo ---------------------------------------------- Old Man | 2048 | Methusela | 9999 Daddy | 1234 | Grandpa | 4321 Kid | 9191 | Daddy | 1234
DISCLAIMER: Yes, I know this is not normalized. I've tried JOINing on the regno, and found it took forever. Reason being, I think, we have a search page that, using DataTables, loads every row in the DB (only the public data from each, though). It doesn't display it all at once, but it loads it, so it can use search input to filter through them client side. So we were basically trying to JOIN the entire table to itself, and it took ~30 seconds per page load.
I know there's probably something I did wrong somehow, and there's a way to do this "properly" but that's not what I'm asking here. Even so, answers to that end are welcome.
Kid is now a teenager, and insists that someone put him in the database wrong, and that his real dad is Old Man, regno 2048.
My question then - can I make a trigger so that, when the registrar changes Kid's sireRegNo to 2048, Kid's sireName column gets changed to "Old Man"?
TRIGGER VERSION (least comfortable with this, least certain of what this looks like to anybody else)
Here's my attempt at an "actual" trigger, just in case there's some significant difference between triggers and stored procedures. Modified from mid way down this page
mysql> delimiter // mysql> CREATE TRIGGER update_sire_name BEFORE UPDATE ON table_name -> FOR EACH ROW #I trust this means "each row that is modified"... -> BEGIN -> IF NEW.sireRegNo = table_name.regNo THEN #searches regNo field of every row in DB for the new value stored in sireRegNo -> SET NEW.sireName = table_name.name -> END IF; -> END;// mysql> delimiter ;
STORED PROCEDURE VERSION (more comfortable, possibly farther from what I need?)
modified from this page
DELIMITER // CREATE PROCEDURE sp3 (IN reg CHAR(50)) BEGIN SELECT name FROM goatdata WHERE regno = NEW.reg; SET NEW.sireName = 'result of above SELECT statement' END // DELIMITER ;
I've not dealt with triggers or stored procedures before, so that looks weird to me and is probably flat wrong in multiple ways. Also, I realize this is for a stored procedure, but as I've been searching, I saw somewhere say triggers ARE stored procedures, so I'm hoping this is clear, and maybe even that I could just replace "procedure" with "trigger"? Maybe? It certainly feels a lot closer to what I'm trying to do, in any case.
REGULAR MYSQL (clearest to me, but obviously not remotely what I need)
SELECT name, regno FROM table_name WHERE regno = 'correct sire regno'; UPDATE table_name SET sireName = 'correct sire name' WHERE regno = 'kid regno'