/*
Navicat Premium Dump SQL
Source Server : 1
Source Server Type : MySQL
Source Server Version : 50744 (5.7.44)
Source Host : localhost:32770
Source Schema : 实习
Target Server Type : MySQL
Target Server Version : 50744 (5.7.44)
File Encoding : 65001
Date: 02/07/2025 16:55:29
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for 出库单
-- ----------------------------
DROP TABLE IF EXISTS `出库单`;
CREATE TABLE `出库单` (
`出库单号` int(11) NOT NULL,
`出库日期` date NULL DEFAULT NULL,
`仓储编号` int(11) NULL DEFAULT NULL,
`出库状态` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`出库单号`) USING BTREE,
INDEX `idx_出库单号`(`出库单号`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for 出库细则
-- ----------------------------
DROP TABLE IF EXISTS `出库细则`;
CREATE TABLE `出库细则` (
`出库细则号` int(11) NOT NULL,
`农产品号` int(11) NULL DEFAULT NULL,
`出库单号` int(11) NULL DEFAULT NULL,
`出库数` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`出库细则号`) USING BTREE,
INDEX `idx_出库细则号`(`出库细则号`) USING BTREE,
INDEX `idx_出库单号_出库细则`(`出库单号`) USING BTREE,
INDEX `idx_农产品编号_出库`(`农产品号`) USING BTREE,
CONSTRAINT `出库细则_ibfk_1` FOREIGN KEY (`农产品号`) REFERENCES `农产品` (`农产品号`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `出库细则_ibfk_2` FOREIGN KEY (`出库单号`) REFERENCES `出库单` (`出库单号`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for 订货单
-- ----------------------------
DROP TABLE IF EXISTS `订货单`;
CREATE TABLE `订货单` (
`订货单号` int(11) NOT NULL,
`订货_time` datetime NULL DEFAULT NULL,
`订单状态` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`订货单号`) USING BTREE,
INDEX `idx_订货单号`(`订货单号`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for 订货细则
-- ----------------------------
DROP TABLE IF EXISTS `订货细则`;
CREATE TABLE `订货细则` (
`订货细则号` int(11) NOT NULL,
`农产品号` int(11) NULL DEFAULT NULL,
`订货单号` int(11) NULL DEFAULT NULL,
`下单数量` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`订货细则号`) USING BTREE,
INDEX `订货单号`(`订货单号`) USING BTREE,
INDEX `idx_订货细则号`(`订货细则号`) USING BTREE,
INDEX `idx_农产品编号_订货`(`农产品号`) USING BTREE,
CONSTRAINT `订货细则_ibfk_1` FOREIGN KEY (`农产品号`) REFERENCES `农产品` (`农产品号`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `订货细则_ibfk_2` FOREIGN KEY (`订货单号`) REFERENCES `订货单` (`订货单号`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for 供应商
-- ----------------------------
DROP TABLE IF EXISTS `供应商`;
CREATE TABLE `供应商` (
`供应商号` int(11) NOT NULL,
`供应商名称` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`供应商电话` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`银行账号` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`供应商号`) USING BTREE,
INDEX `idx_供应商号`(`供应商号`) USING BTREE,
INDEX `idx_供应商名称`(`供应商名称`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for 购买流水
-- ----------------------------
DROP TABLE IF EXISTS `购买流水`;
CREATE TABLE `购买流水` (
`流水单号` int(11) NOT NULL,
`顾客编号` int(11) NULL DEFAULT NULL,
`流水日期` datetime NULL DEFAULT NULL,
`流水类型` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`金额` decimal(10, 2) NULL DEFAULT NULL,
PRIMARY KEY (`流水单号`) USING BTREE,
INDEX `idx_流水单号_购买流水`(`流水单号`) USING BTREE,
INDEX `idx_顾客编号`(`顾客编号`) USING BTREE,
CONSTRAINT `购买流水_ibfk_1` FOREIGN KEY (`顾客编号`) REFERENCES `顾客` (`顾客编号`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for 购买细则
-- ----------------------------
DROP TABLE IF EXISTS `购买细则`;
CREATE TABLE `购买细则` (
`购买细则号` int(11) NOT NULL,
`农产品号` int(11) NULL DEFAULT NULL,
`购买流水单号` int(11) NULL DEFAULT NULL,
`购买数量` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`购买细则号`) USING BTREE,
INDEX `idx_购买细则号`(`购买细则号`) USING BTREE,
INDEX `idx_购买流水单号`(`购买流水单号`) USING BTREE,
INDEX `idx_农产品编号_购买`(`农产品号`) USING BTREE,
CONSTRAINT `购买细则_ibfk_1` FOREIGN KEY (`农产品号`) REFERENCES `农产品` (`农产品号`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `购买细则_ibfk_2` FOREIGN KEY (`购买流水单号`) REFERENCES `购买流水` (`流水单号`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for 顾客
-- ----------------------------
DROP TABLE IF EXISTS `顾客`;
CREATE TABLE `顾客` (
`顾客编号` int(11) NOT NULL,
`顾客名` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`收货地址` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`联系方式` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`身份` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`顾客编号`) USING BTREE,
INDEX `idx_顾客名`(`顾客名`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for 货架
-- ----------------------------
DROP TABLE IF EXISTS `货架`;
CREATE TABLE `货架` (
`货架编号` int(11) NOT NULL,
`农产品号` int(11) NULL DEFAULT NULL,
`货架位置` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`货架已占用容量` int(11) NULL DEFAULT NULL,
`货架总容量` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`货架编号`) USING BTREE,
INDEX `idx_货架编号`(`货架编号`) USING BTREE,
INDEX `idx_农产品编号_货架`(`农产品号`) USING BTREE,
CONSTRAINT `货架_ibfk_1` FOREIGN KEY (`农产品号`) REFERENCES `农产品` (`农产品号`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for 交易反馈单
-- ----------------------------
DROP TABLE IF EXISTS `交易反馈单`;
CREATE TABLE `交易反馈单` (
`交易反馈单单号` int(11) NOT NULL DEFAULT 0,
`交易_time` datetime NULL DEFAULT NULL,
`交易状态` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`订单单号` int(11) NULL DEFAULT 0,
PRIMARY KEY (`交易反馈单单号`) USING BTREE,
INDEX `idx_交易反馈单单号`(`交易反馈单单号`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for 交易细则
-- ----------------------------
DROP TABLE IF EXISTS `交易细则`;
CREATE TABLE `交易细则` (
`交易细则号` int(11) NOT NULL DEFAULT 0,
`农产品号` int(11) NULL DEFAULT NULL,
`交易反馈单单号` int(11) NULL DEFAULT 0,
`交易数量` int(11) NULL DEFAULT 0,
PRIMARY KEY (`交易细则号`) USING BTREE,
INDEX `idx_交易细则号`(`交易细则号`) USING BTREE,
INDEX `idx_交易反馈单单号`(`交易反馈单单号`) USING BTREE,
INDEX `idx_农产品编号_交易`(`农产品号`) USING BTREE,
CONSTRAINT `交易细则_ibfk_1` FOREIGN KEY (`农产品号`) REFERENCES `农产品` (`农产品号`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `交易细则_ibfk_2` FOREIGN KEY (`交易反馈单单号`) REFERENCES `交易反馈单` (`交易反馈单单号`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for 进货单
-- ----------------------------
DROP TABLE IF EXISTS `进货单`;
CREATE TABLE `进货单` (
`进货单号` int(11) NOT NULL DEFAULT 0,
`进货日期` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
`供应商号` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`进货单号`) USING BTREE,
INDEX `idx_进货单号`(`进货单号`) USING BTREE,
INDEX `idx_供应商号_进货单`(`供应商号`) USING BTREE,
CONSTRAINT `进货单_ibfk_1` FOREIGN KEY (`供应商号`) REFERENCES `供应商` (`供应商号`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for 进货细则
-- ----------------------------
DROP TABLE IF EXISTS `进货细则`;
CREATE TABLE `进货细则` (
`进货细则号` int(11) NOT NULL DEFAULT 0,
`农产品号` int(11) NOT NULL DEFAULT 0,
`进货单号` int(11) NOT NULL DEFAULT 0,
`采购数量` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`进货细则号`) USING BTREE,
INDEX `idx_进货细则号`(`进货细则号`) USING BTREE,
INDEX `idx_农产品号_进货`(`农产品号`) USING BTREE,
INDEX `idx_进货单号_进货细则`(`进货单号`) USING BTREE,
CONSTRAINT `进货细则_ibfk_1` FOREIGN KEY (`农产品号`) REFERENCES `农产品` (`农产品号`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `进货细则_ibfk_2` FOREIGN KEY (`进货单号`) REFERENCES `进货单` (`进货单号`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for 库存流水
-- ----------------------------
DROP TABLE IF EXISTS `库存流水`;
CREATE TABLE `库存流水` (
`流水单号` int(11) NOT NULL DEFAULT 0,
`流水日期` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`流水类型` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`金额` decimal(10, 2) NOT NULL DEFAULT 0.00,
`单号` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`流水单号`) USING BTREE,
INDEX `idx_流水单号_库存流水`(`流水单号`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for 库存流水细则
-- ----------------------------
DROP TABLE IF EXISTS `库存流水细则`;
CREATE TABLE `库存流水细则` (
`库存流水细则号` int(11) NOT NULL DEFAULT 0 AUTO_INCREMENT,
`流水单号` int(11) NOT NULL DEFAULT 0,
`农产品号` int(11) NOT NULL DEFAULT 0,
`变动数量` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`库存流水细则号`) USING BTREE,
INDEX `idx_流水单号_库存流水细则`(`流水单号`) USING BTREE,
INDEX `idx_农产品编号_库存流水`(`农产品号`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 200050 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for 库存信息
-- ----------------------------
DROP TABLE IF EXISTS `库存信息`;
CREATE TABLE `库存信息` (
`农产品号` int(11) NOT NULL,
`当前库存数量` int(11) NOT NULL DEFAULT 0,
`安全库存数量` int(11) NOT NULL DEFAULT 0,
`库存更新_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`农产品号`) USING BTREE,
INDEX `idx_当前库存数量`(`当前库存数量`) USING BTREE,
INDEX `idx_农产品编号_库存`(`农产品号`) USING BTREE,
CONSTRAINT `库存信息_ibfk_1` FOREIGN KEY (`农产品号`) REFERENCES `农产品` (`农产品号`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for 农产品
-- ----------------------------
DROP TABLE IF EXISTS `农产品`;
CREATE TABLE `农产品` (
`农产品号` int(11) NOT NULL,
`产品名称` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`产品进价` decimal(10, 2) NOT NULL DEFAULT 0.00,
`产品售价` decimal(10, 2) NOT NULL DEFAULT 0.00,
`产品质量` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
PRIMARY KEY (`农产品号`) USING BTREE,
INDEX `idx_农产品号`(`农产品号`) USING BTREE,
INDEX `idx_产品名称`(`产品名称`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for 缺货单
-- ----------------------------
DROP TABLE IF EXISTS `缺货单`;
CREATE TABLE `缺货单` (
`缺货单号` int(11) NOT NULL DEFAULT 0,
`缺货时间` datetime NULL DEFAULT NULL,
`预计进货日期` datetime NULL DEFAULT NULL,
PRIMARY KEY (`缺货单号`) USING BTREE,
INDEX `idx_缺货单号`(`缺货单号`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for 缺货细则
-- ----------------------------
DROP TABLE IF EXISTS `缺货细则`;
CREATE TABLE `缺货细则` (
`缺货细则号` int(11) NOT NULL DEFAULT 0 AUTO_INCREMENT,
`缺货单号` int(11) NULL DEFAULT 0,
`农产品号` int(11) NULL DEFAULT NULL,
`缺货数量` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`缺货细则号`) USING BTREE,
INDEX `idx_缺货细则号`(`缺货细则号`) USING BTREE,
INDEX `idx_缺货单号_缺货细则`(`缺货单号`) USING BTREE,
INDEX `idx_农产品编号_缺货`(`农产品号`) USING BTREE,
CONSTRAINT `缺货细则_ibfk_1` FOREIGN KEY (`缺货单号`) REFERENCES `缺货单` (`缺货单号`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `缺货细则_ibfk_2` FOREIGN KEY (`农产品号`) REFERENCES `农产品` (`农产品号`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 800026 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for 入库单
-- ----------------------------
DROP TABLE IF EXISTS `入库单`;
CREATE TABLE `入库单` (
`入库单号` int(11) NOT NULL DEFAULT 0,
`入库日期` date NOT NULL DEFAULT '1970-01-01',
PRIMARY KEY (`入库单号`) USING BTREE,
INDEX `idx_入库单号`(`入库单号`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for 入库细则
-- ----------------------------
DROP TABLE IF EXISTS `入库细则`;
CREATE TABLE `入库细则` (
`入库细则号` int(11) NOT NULL DEFAULT 0,
`入库单号` int(11) NOT NULL DEFAULT 0,
`农产品号` int(11) NOT NULL DEFAULT 0,
`入库数量` int(11) NOT NULL DEFAULT 0,
INDEX `idx_入库细则号`(`入库细则号`) USING BTREE,
INDEX `idx_入库单号_入库细则`(`入库单号`) USING BTREE,
INDEX `idx_农产品编号_入库`(`农产品号`) USING BTREE,
CONSTRAINT `入库细则_ibfk_1` FOREIGN KEY (`入库单号`) REFERENCES `入库单` (`入库单号`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `入库细则_ibfk_2` FOREIGN KEY (`农产品号`) REFERENCES `农产品` (`农产品号`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for 退货单
-- ----------------------------
DROP TABLE IF EXISTS `退货单`;
CREATE TABLE `退货单` (
`退货单号` int(11) NOT NULL,
`退货申请_time` datetime NULL DEFAULT NULL,
`退货审批_time` datetime NULL DEFAULT NULL,
`退货金额` decimal(10, 2) NULL DEFAULT NULL,
`退货地址` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`退货审批人` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`状态` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`退货单号`) USING BTREE,
INDEX `idx_退货单号`(`退货单号`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for 退货细则
-- ----------------------------
DROP TABLE IF EXISTS `退货细则`;
CREATE TABLE `退货细则` (
`退货细则号` int(11) NOT NULL,
`农产品号` int(11) NULL DEFAULT NULL,
`退货单号` int(11) NULL DEFAULT NULL,
`退货数量` int(11) NULL DEFAULT NULL,
`退货原因` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`退货细则号`) USING BTREE,
INDEX `idx_退货细则号`(`退货细则号`) USING BTREE,
INDEX `idx_退货单号_退货细则`(`退货单号`) USING BTREE,
INDEX `idx_农产品编号_退货`(`农产品号`) USING BTREE,
CONSTRAINT `退货细则_ibfk_1` FOREIGN KEY (`农产品号`) REFERENCES `农产品` (`农产品号`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `退货细则_ibfk_2` FOREIGN KEY (`退货单号`) REFERENCES `退货单` (`退货单号`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Triggers structure for table 出库细则
-- ----------------------------
DROP TRIGGER IF EXISTS `validate_shelf_capacity_before_out`;
delimiter ;;
CREATE TRIGGER `validate_shelf_capacity_before_out` BEFORE INSERT ON `出库细则` FOR EACH ROW BEGIN
DECLARE shelf_capacity INT;
DECLARE shelf_used INT;
-- 获取对应货架容量信息
SELECT 货架总容量, 货架已占用容量 INTO shelf_capacity, shelf_used
FROM 货架
WHERE 农产品号 = NEW.农产品号
LIMIT 1;
-- 验证出库数量是否超过货架容量
IF (shelf_used - NEW.出库数) < 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '出库数量超过货架当前容量';
END IF;
END
;;
delimiter ;
-- ----------------------------
-- Triggers structure for table 出库细则
-- ----------------------------
DROP TRIGGER IF EXISTS `trg_validate_shelf_capacity`;
delimiter ;;
CREATE TRIGGER `trg_validate_shelf_capacity` BEFORE INSERT ON `出库细则` FOR EACH ROW BEGIN
DECLARE shelf_capacity INT;
DECLARE shelf_used INT;
-- 获取货架容量信息
SELECT 货架总容量, 货架已占用容量 INTO shelf_capacity, shelf_used
FROM 货架
WHERE 农产品号 = NEW.农产品号;
-- 验证出库数量是否可行
IF NEW.出库数 > shelf_used THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '错误: 出库数量超过货架当前容量';
END IF;
END
;;
delimiter ;
-- ----------------------------
-- Triggers structure for table 出库细则
-- ----------------------------
DROP TRIGGER IF EXISTS `trg_before_insert_stock_out`;
delimiter ;;
CREATE TRIGGER `trg_before_insert_stock_out` BEFORE INSERT ON `出库细则` FOR EACH ROW BEGIN
DECLARE current_stock INT;
-- 获取当前库存
SELECT 当前库存数量 INTO current_stock
FROM 库存信息
WHERE 农产品号 = NEW.农产品号;
-- 验证库存是否足够
IF current_stock < NEW.出库数 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '错误: 库存不足，无法出库';
END IF;
END
;;
delimiter ;
-- ----------------------------
-- Triggers structure for table 购买细则
-- ----------------------------
DROP TRIGGER IF EXISTS `trg_update_purchase_amount`;
delimiter ;;
CREATE TRIGGER `trg_update_purchase_amount` AFTER INSERT ON `购买细则` FOR EACH ROW BEGIN
DECLARE product_price DECIMAL(10,2);
DECLARE amount DECIMAL(10,2);
-- 获取产品单价
SELECT 产品售价 INTO product_price
FROM 农产品
WHERE 农产品号 = NEW.农产品号;
-- 计算金额
SET amount = product_price * NEW.购买数量;
-- 更新购买流水总金额
UPDATE 购买流水
SET 金额 = IFNULL(金额, 0) + amount
WHERE 流水单号 = NEW.购买流水单号;
END
;;
delimiter ;
-- ----------------------------
-- Triggers structure for table 购买细则
-- ----------------------------
DROP TRIGGER IF EXISTS `trg_generate_transaction_feedback`;
delimiter ;;
CREATE TRIGGER `trg_generate_transaction_feedback` AFTER INSERT ON `购买细则` FOR EACH ROW BEGIN
DECLARE transaction_count INT DEFAULT 0;
DECLARE transaction_id INT;
-- 检查是否已完成购买
SELECT COUNT(*) INTO transaction_count
FROM 交易反馈单
WHERE 订单单号 = NEW.购买流水单号;
-- 如果还没有交易反馈单，创建新的
IF transaction_count = 0 THEN
INSERT INTO 交易反馈单 (交易_time, 交易状态, 订单单号)
VALUES (NOW(), '处理中', NEW.购买流水单号);
SET transaction_id = LAST_INSERT_ID();
ELSE
SELECT 交易反馈单单号 INTO transaction_id
FROM 交易反馈单
WHERE 订单单号 = NEW.购买流水单号;
END IF;
-- 添加交易细则
INSERT INTO 交易细则 (农产品号, 交易反馈单单号, 交易数量)
VALUES (NEW.农产品号, transaction_id, NEW.购买数量);
END
;;
delimiter ;
-- ----------------------------
-- Triggers structure for table 购买细则
-- ----------------------------
DROP TRIGGER IF EXISTS `trg_after_insert_purchase_detail`;
delimiter ;;
CREATE TRIGGER `trg_after_insert_purchase_detail` AFTER INSERT ON `购买细则` FOR EACH ROW BEGIN
-- 更新库存数量
UPDATE 库存信息
SET 当前库存数量 = 当前库存数量 - NEW.购买数量,
库存更新_time = NOW()
WHERE 农产品号 = NEW.农产品号;
END
;;
delimiter ;
-- ----------------------------
-- Triggers structure for table 进货细则
-- ----------------------------
DROP TRIGGER IF EXISTS `防止0主键_进货细则`;
delimiter ;;
CREATE TRIGGER `防止0主键_进货细则` BEFORE INSERT ON `进货细则` FOR EACH ROW BEGIN
IF NEW.进货细则号 = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '主键值不能为0';
END IF;
END
;;
delimiter ;
-- ----------------------------
-- Triggers structure for table 库存流水细则
-- ----------------------------
DROP TRIGGER IF EXISTS `防止0主键_库存流水细则`;
delimiter ;;
CREATE TRIGGER `防止0主键_库存流水细则` BEFORE INSERT ON `库存流水细则` FOR EACH ROW BEGIN
IF NEW.库存流水细则号 = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '主键值不能为0';
END IF;
END
;;
delimiter ;
-- ----------------------------
-- Triggers structure for table 库存流水细则
-- ----------------------------
DROP TRIGGER IF EXISTS `update_inventory_after_stock_flow`;
delimiter ;;
CREATE TRIGGER `update_inventory_after_stock_flow` AFTER INSERT ON `库存流水细则` FOR EACH ROW BEGIN
DECLARE product_id INT;
DECLARE change_quantity INT;
SET product_id = NEW.农产品号;
SET change_quantity = NEW.变动数量;
UPDATE 库存信息
SET 当前库存数量 = 当前库存数量 + change_quantity,
库存更新_time = NOW()
WHERE 农产品号 = product_id;
END
;;
delimiter ;
-- ----------------------------
-- Triggers structure for table 库存流水细则
-- ----------------------------
DROP TRIGGER IF EXISTS `trg_update_inventory`;
delimiter ;;
CREATE TRIGGER `trg_update_inventory` AFTER INSERT ON `库存流水细则` FOR EACH ROW BEGIN
UPDATE 库存信息
SET
当前库存数量 = 当前库存数量 + NEW.变动数量,
库存更新_time = NOW()
WHERE 农产品号 = NEW.农产品号;
END
;;
delimiter ;
-- ----------------------------
-- Triggers structure for table 库存信息
-- ----------------------------
DROP TRIGGER IF EXISTS `trg_check_safety_stock`;
delimiter ;;
CREATE TRIGGER `trg_check_safety_stock` AFTER UPDATE ON `库存信息` FOR EACH ROW BEGIN
IF NEW.当前库存数量 < NEW.安全库存数量 THEN
INSERT INTO 缺货单 (缺货时间, 预计进货日期)
VALUES (NOW(), DATE_ADD(NOW(), INTERVAL 7 DAY));
SET @last_out_of_stock_id = LAST_INSERT_ID();
INSERT INTO 缺货细则 (缺货单号, 农产品号, 缺货数量)
VALUES (@last_out_of_stock_id, NEW.农产品号,
(NEW.安全库存数量 - NEW.当前库存数量));
END IF;
END
;;
delimiter ;
-- ----------------------------
-- Triggers structure for table 入库细则
-- ----------------------------
DROP TRIGGER IF EXISTS `防止0主键_入库细则`;
delimiter ;;
CREATE TRIGGER `防止0主键_入库细则` BEFORE INSERT ON `入库细则` FOR EACH ROW BEGIN
IF NEW.入库细则号 = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '主键值不能为0';
END IF;
END
;;
delimiter ;
-- ----------------------------
-- Triggers structure for table 入库细则
-- ----------------------------
DROP TRIGGER IF EXISTS `trg_after_insert_stock_in`;
delimiter ;;
CREATE TRIGGER `trg_after_insert_stock_in` AFTER INSERT ON `入库细则` FOR EACH ROW BEGIN
-- 更新库存数量
UPDATE 库存信息
SET 当前库存数量 = 当前库存数量 + NEW.入库数量,
库存更新_time = NOW()
WHERE 农产品号 = NEW.农产品号;
-- 记录库存流水
INSERT INTO 库存流水 (流水日期, 流水类型, 单号)
VALUES (NOW(), '入库', NEW.入库单号);
END
;;
delimiter ;
-- ----------------------------
-- Triggers structure for table 退货细则
-- ----------------------------
DROP TRIGGER IF EXISTS `trg_after_insert_return_detail`;
delimiter ;;
CREATE TRIGGER `trg_after_insert_return_detail` AFTER INSERT ON `退货细则` FOR EACH ROW BEGIN
-- 更新库存数量
UPDATE 库存信息
SET 当前库存数量 = 当前库存数量 + NEW.退货数量,
库存更新_time = NOW()
WHERE 农产品号 = NEW.农产品号;
END
;;
delimiter ;
SET FOREIGN_KEY_CHECKS = 1;
分别生成存储过程，包括：顾客退货，农产品入库，进货
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
2973
