Can a MySQL trigger include a where clause

Question

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'

Answer 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

---

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

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