mysql - 编写触发器以使用自己行中的值更新值

我有下表。

CREATE TABLE `community_data_1` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `type` VARCHAR(1) NOT NULL,
    `reply_to_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
    `subject` TEXT NOT NULL,
    `post` MEDIUMTEXT NOT NULL,
    `html` VARCHAR(1) NOT NULL DEFAULT '0',
    `time_stamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `author_id` BIGINT(20) UNSIGNED NOT NULL,
    `d_id` BIGINT(20) UNSIGNED NOT NULL,
    PRIMARY KEY (`id`)
)
ENGINE=MyISAM
ROW_FORMAT=DEFAULT

现在，每当插入完成时，在插入之后，我需要检查d_id值是否设置为零，如果设置为零，则需要将其更新为与该行id相同的值。我该怎么做？

最佳答案

delimiter |

CREATE TRIGGER fixValue BEFORE INSERT ON community_data_1
  FOR EACH ROW BEGIN

    IF NEW.d_id = 0 THEN
        SET @NewId= (SELECT MAX(id)+1 FROM community_data_1);
        SET NEW.d_id = NewId;
    END IF;
  END;
|

delimiter ;