Eu preciso criar um gatilho que, sempre que a coluna sireRegNo de uma cabra for atualizada, sua coluna sireName seja atualizada automaticamente para corresponder.

Eu tenho um banco de dados que armazena a genealogia animal. Cada linha armazena os nomes e os #s de registro de seus pais. O campo sireName é somente leitura, mas os registradores podem alterar sireRegno.

Criar declaração:

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;

dados de exemplo:

table_name

name    |   regNo   |   sireName    | sireRegNo
----------------------------------------------
Old Man |   2048    |   Methusela   |   9999
Daddy   |   1234    |   Grandpa     |   4321
Kid     |   9191    |   Daddy       |   1234

IMPORTANTE: Sim, eu sei que isso não é normalizado. Eu tentei JOINing no regno, e descobri que demorou uma eternidade. A razão é, eu acho, que temos uma página de pesquisa que, usando DataTables, carrega todas as linhas do banco de dados (apenas os dados públicos de cada uma). Ele não exibe tudo de uma vez, mas o carrega, para que possa usar a entrada de pesquisa para filtrá-los no lado do cliente. Então, basicamente, estávamos tentando JOIN a tabela inteira para si mesma, e demorava cerca de 30 segundos por carregamento de página.

Eu sei que provavelmente há algo que eu fiz de errado de alguma forma, e há uma maneira de fazer isso "corretamente", mas não é isso que estou perguntando aqui. Mesmo assim, respostas nesse sentido são bem-vindas.

Kid agora é um adolescente e insiste que alguém o colocou no banco de dados errado e que seu verdadeiro pai é Old Man, regno 2048.

Minha pergunta então - posso fazer um gatilho para que, quando o registrador alterar o sireRegNo do Kid para 2048, a coluna sireName do Kid seja alterada para "Old Man"?

VERSÃO DE GATILHO (menos confortável com isso, menos certo do que isso parece para qualquer outra pessoa)

Aqui está minha tentativa de um gatilho "real", apenas no caso de haver alguma diferença significativa entre gatilhos e procedimentos armazenados. Modificado a partir do meio desta página

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 ;

VERSÃO DO PROCEDIMENTO ARMAZENADO (mais confortável, possivelmente mais distante do que preciso?)

modificado a partir desta página

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 ;

Eu não lidei com gatilhos ou procedimentos armazenados antes, então isso parece estranho para mim e provavelmente está errado de várias maneiras. Além disso, percebo que isso é para um procedimento armazenado, mas, enquanto pesquisava, vi em algum lugar dizer que os gatilhos SÃO procedimentos armazenados, então espero que isso esteja claro e talvez até que eu possa substituir "procedimento" por " acionar"? Pode ser? Certamente parece muito mais próximo do que estou tentando fazer, em qualquer caso.

REGULAR MYSQL (mais claro para mim, mas obviamente não remotamente o que eu preciso)

SELECT name, regno FROM table_name WHERE regno = 'correct sire regno';
UPDATE table_name SET sireName = 'correct sire name' WHERE regno = 'kid regno'