File indexing completed on 2024-04-21 06:00:36
0001 CREATE TABLE `member_external_id` 0002 ( 0003 `external_id` VARCHAR(255) NOT NULL, 0004 `member_id` INT(11) NOT NULL, 0005 `created_at` DATETIME NULL, 0006 `is_deleted` INT(1) NULL, 0007 PRIMARY KEY (`external_id`), 0008 INDEX `idx_member` (`member_id` ASC) 0009 ) 0010 ENGINE = InnoDB; 0011 0012 DROP TRIGGER IF EXISTS `member_external_id_BEFORE_INSERT`; 0013 0014 DELIMITER $$ 0015 CREATE DEFINER = CURRENT_USER TRIGGER `member_external_id_BEFORE_INSERT` 0016 BEFORE INSERT 0017 ON `member_external_id` 0018 FOR EACH ROW 0019 BEGIN 0020 IF `NEW`.`created_at` IS NULL 0021 THEN 0022 SET `NEW`.`created_at` = NOW(); 0023 END IF; 0024 END$$ 0025 DELIMITER ; 0026 0027 INSERT INTO `member_external_id` (`external_id`, `member_id`) 0028 SELECT SUBSTR(SHA(`member_id`), 1, 20) AS `external_id`, 0029 `member_id` 0030 FROM `member` 0031 WHERE `is_active` = 1 0032 AND `is_deleted` = 0 0033 ORDER BY `member_id`;