81 lines
3.3 KiB
SQL
81 lines
3.3 KiB
SQL
/*
|
||
Navicat Premium Dump SQL
|
||
|
||
Source Server : A-OMS-LB-DB
|
||
Source Server Type : MySQL
|
||
Source Server Version : 80028 (8.0.28)
|
||
Source Host : 172.233.222.200:7033
|
||
Source Schema : lr01mainusa
|
||
|
||
Target Server Type : MySQL
|
||
Target Server Version : 80028 (8.0.28)
|
||
File Encoding : 65001
|
||
|
||
Date: 18/03/2026 11:09:47
|
||
*/
|
||
|
||
SET NAMES utf8mb4;
|
||
SET FOREIGN_KEY_CHECKS = 0;
|
||
|
||
-- ----------------------------
|
||
-- Table structure for label_replace_requests
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `label_replace_requests`;
|
||
CREATE TABLE `label_replace_requests` (
|
||
`Id` int NOT NULL AUTO_INCREMENT,
|
||
`BillOfLadingNumber` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
|
||
`MasterPackageNumber` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
|
||
`ReferenceNumber` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
|
||
`NeutralWaybillNumber` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '中性面单单号（必填）',
|
||
`FinalMileTrackingNumber` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
|
||
`Label` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
|
||
`ReplaceStatus` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Y',
|
||
`CreatedAt` datetime NOT NULL COMMENT '创建时间',
|
||
`UpdatedAt` datetime NOT NULL COMMENT '更新时间',
|
||
`CustomerId` int NULL DEFAULT NULL COMMENT '客户id',
|
||
`LabelRetrievedAt` datetime NULL DEFAULT NULL COMMENT '获取标签时间',
|
||
PRIMARY KEY (`Id`) USING BTREE,
|
||
UNIQUE INDEX `idx_neutral_waybill_number`(`NeutralWaybillNumber` ASC) USING BTREE,
|
||
INDEX `idx_created_at`(`CreatedAt` ASC) USING BTREE,
|
||
INDEX `idx_replace_status`(`ReplaceStatus` ASC) USING BTREE
|
||
) ENGINE = InnoDB AUTO_INCREMENT = 7150 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;
|
||
|
||
-- ----------------------------
|
||
-- Triggers structure for table label_replace_requests
|
||
-- ----------------------------
|
||
DROP TRIGGER IF EXISTS `insert_label_retrieved_timestamp`;
|
||
delimiter ;;
|
||
CREATE TRIGGER `insert_label_retrieved_timestamp` BEFORE INSERT ON `label_replace_requests` FOR EACH ROW BEGIN
|
||
-- 当插入数据时提供了Label字段，设置LabelRetrievedAt
|
||
IF NEW.Label IS NOT NULL THEN
|
||
SET NEW.LabelRetrievedAt = UTC_TIMESTAMP();
|
||
END IF;
|
||
-- 确保CreatedAt和UpdatedAt字段有值
|
||
IF NEW.CreatedAt IS NULL THEN
|
||
SET NEW.CreatedAt = UTC_TIMESTAMP();
|
||
END IF;
|
||
IF NEW.UpdatedAt IS NULL THEN
|
||
SET NEW.UpdatedAt = UTC_TIMESTAMP();
|
||
END IF;
|
||
END
|
||
;;
|
||
delimiter ;
|
||
|
||
-- ----------------------------
|
||
-- Triggers structure for table label_replace_requests
|
||
-- ----------------------------
|
||
DROP TRIGGER IF EXISTS `update_label_retrieved_timestamp`;
|
||
delimiter ;;
|
||
CREATE TRIGGER `update_label_retrieved_timestamp` BEFORE UPDATE ON `label_replace_requests` FOR EACH ROW BEGIN
|
||
-- 当Label字段被设置或修改时，更新LabelRetrievedAt
|
||
IF NEW.Label IS NOT NULL AND (OLD.Label IS NULL OR NEW.Label != OLD.Label) THEN
|
||
SET NEW.LabelRetrievedAt = UTC_TIMESTAMP();
|
||
END IF;
|
||
-- 同时更新UpdatedAt字段
|
||
SET NEW.UpdatedAt = UTC_TIMESTAMP();
|
||
END
|
||
;;
|
||
delimiter ;
|
||
|
||
SET FOREIGN_KEY_CHECKS = 1;
|