1

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 statement:

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;

example data:

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'
  • Yes, you can make such a trigger. It'll likely contain an if statement rather than a where clause though. What's your real question? – mustaccio Aug 22 at 17:15
  • @mustaccio Would it make a difference if I changed "can I make a trigger that does this" to "HOW can I make a trigger that does this"? – donutguy640 Aug 22 at 18:53
  • Probably not. What would make a difference is a question phrased something like "I followed examples in the MySQL manual to write a trigger [source code included here] that I expect to do [something]. Instead it appears to do [something else]. What do I need to change?" Please also consider reading How to Ask. – mustaccio Aug 22 at 18:55
  • "I've tried JOINing on the regno, and found it took forever." - it would be good to elaborate on this,because normalisation has many benefits over a trigger solution. – ThatDataGuy Aug 22 at 19:57
  • Done. Sorry to you both, I guess I'm used to being too wordy for people, and going off on tangents. It's a weird feeling when it appears I've been too concise. – donutguy640 Aug 22 at 20:12
1

You're almost there. Your trigger should probably look like this:

CREATE TRIGGER update_sire_name before UPDATE ON table_name
for each row

    set new.sireName = 
    (select name from table_name where regNo = new.sireRegNo)

dbfiddle


1 Yes, there can be a where clause in a trigger.

2 Consider using numeric data types (e.g. INTEGER) where values are clearly numeric (e.g. regNo)

  • Sometimes, it really helps to have 2 computers...I couldn't get this to work, but my partner could, so I finally tried it on the other, and it worked. I wish I knew what was different though, grrr!) I wiped the schema here and restored to an export from the other one, and it works. I THOUGHT we've been pretty dang careful to keep them matching, but I guess not O_o – donutguy640 Aug 23 at 17:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.