mysql - 更新前更新同一个表中的不同行

Question

我有一张家庭关系表。这些关系之一是配偶。当插入或更新行(单独的触发器)时，如果存在配偶的 ID，我想更新相关配偶记录中的 ID。

IOW，如果更新了 ID 为 2 的行并且其配偶 ID 为 3，我希望 ID 为 3 的行的配偶 ID 设置为 2。

这是该表的架构:

CREATE TABLE IF NOT EXISTS `gohman_birthdays` (
    `gb_id`         INT(11) UNSIGNED        NOT NULL    AUTO_INCREMENT                  COMMENT 'Primary Key',
    `gb_created`    TIMESTAMP                           DEFAULT '0000-00-00 00:00:00'   COMMENT 'Timestamp when record was created',
    `gb_modified`   TIMESTAMP                           DEFAULT '0000-00-00 00:00:00'   COMMENT 'Timestamp when record was last modified',
    `gb_name`       VARCHAR(30)             NULL        DEFAULT ''                      COMMENT 'Full Name',
    `gb_lname`      VARCHAR(30)             NULL        DEFAULT 'Gohman'                COMMENT 'Last Name',
    `gb_maidenname` VARCHAR(30)             NULL        DEFAULT ''                      COMMENT 'Maiden Name',
    `gb_nickname`   VARCHAR(30)             NULL        DEFAULT ''                      COMMENT 'Nick-Name',
    `gb_email`      VARCHAR(100)            NULL        DEFAULT ''                      COMMENT 'Email Address',
    `gb_phone1`     VARCHAR(30)             NULL        DEFAULT ''                      COMMENT '1st Phone',
    `gb_phone2`     VARCHAR(30)             NULL        DEFAULT ''                      COMMENT '2nd Phone',
    `gb_dob`        DATE                    NULL        DEFAULT 1                       COMMENT 'Date Of Birth',
    `gb_dod`        DATE                    NULL        DEFAULT 1                       COMMENT 'Date Of Death',
    `gb_fatherid`   INT(11)                 NULL        DEFAULT NULL                    COMMENT 'FK to father parent ID',
    `gb_motherid`   INT(11)                 NULL        DEFAULT NULL                    COMMENT 'FK to mother parent ID',
    `gb_spouseid`   INT(11)                 NULL        DEFAULT NULL                    COMMENT 'FK to mother parent ID',
    `gb_gender`     TINYINT(1)              NULL        DEFAULT NULL                    COMMENT '1 if male, 0 if female',
    `gb_deceased`   TINYINT(1)              NULL        DEFAULT 0                       COMMENT '0 or 1 flag, 1 if deceased',
    PRIMARY KEY (`gb_id`),
    KEY `idx_gb_nickname` (`gb_nickname`),
    KEY `idx_gb_email` (`gb_email`),
    KEY `idx_gb_name` (`gb_name`),
    KEY `idx_gb_dob` (`gb_dob`),
) 
    ENGINE=MyISAM 
    AUTO_INCREMENT=1 
    DEFAULT CHARSET=utf8 
    COLLATE=utf8_unicode_ci
    COMMENT '';

这是我到目前为止所掌握的触发器:

-- ==============================================================================================
-- TRIGGER: Update created and modified timestamps on gohman_birthdays
-- ==============================================================================================
DELIMITER |

DROP TRIGGER IF EXISTS `gohman_birthdays_CreatedTS`|
DROP TRIGGER IF EXISTS `gohman_birthdays_ModifiedTS`|

CREATE TRIGGER `gohman_birthdays_CreatedTS` BEFORE INSERT ON `gohman_birthdays`
FOR EACH ROW
BEGIN
    SET NEW.`gb_created` = CURRENT_TIMESTAMP;
    SET NEW.`gb_modified` = CURRENT_TIMESTAMP;
END|

CREATE TRIGGER `gohman_birthdays_ModifiedTS` BEFORE UPDATE ON `gohman_birthdays`
FOR EACH ROW
BEGIN
    SET NEW.`gb_modified` = CURRENT_TIMESTAMP;
    IF NOT NEW.`gb_spouseid` IS NULL THEN
        UPDATE `gohman_birthdays` a
            SET a.`gb_spouseid` = NEW.`gb_id`
            WHERE a.`gb_id` = NEW.`gb_spouseid`;
    END IF
END|

DELIMITER ;

问题:

• 我可以更新触发器内的不同行吗？
• 应该位于 BEFORE UPDATE/INSERT 触发器中还是 AFTER 触发器中？
• 应该放在存储过程中吗？

Answer 1

触发器无法更改启动触发器的查询引用的任何表中的数据。

这不是同一个问题，而是我更详细的答案here适用。

<小时/>

更好的解决方案是创建一个单独的表来实际保存关系(而不是明显越来越错误命名的“生日”表中的几个引用字段)。这个新表看起来像:

Person1_id INT(11) UNSIGNED NOT NULL
Person2_id INT(11) UNSIGNED NOT NULL
Relationship_id INT(11) UNSIGNED NOT NULL

Relationship_id 将引用一个新的 Relationships 表，其中包含有关方向术语的信息。示例:

1, "Parent", "Child"
2, "Spouse", "Spouse"
3, "Sibling", "Sibling"
4, "Aunt", "Niece"
etc....

...虽然 sibling 和阿姨侄女关系可以通过连接来计算。