mysql触发器部分工作
mysql触发器部分工作
提问于 2012-02-14 15:23:58
我的应用程序需要5个不同的车辆类别，每个类别的车辆都有一些共同的领域。所以我所做的是为5类车辆创建了5个表vehicle1，vehicle2，vehicle3，vehicle4，vehicle5，然后创建了第6个表' vehicle‘来存储每个车辆通用的字段。现在，每当我输入与特定车辆相关的信息时，就会执行一个触发器，该触发器将公共字段插入到vehicle表中。所以触发器看起来像这样
CREATE TRIGGER `tr_vehicle1_info` AFTER INSERT ON `vehicle1`
FOR EACH ROW insert into vehicle(categ,year,make,model,vin,user_id,principal_driver) values (1,new.year,new.make,new.model,new.vin,new.user_id,new.principal_driver)
CREATE TRIGGER `tr_vehicle1_info` AFTER INSERT ON `vehicle2`
FOR EACH ROW insert into vehicle(categ,year,make,model,vin,user_id,principal_driver) values (2,new.year,new.make,new.model,new.vin,new.user_id,new.principal_driver)
CREATE TRIGGER `tr_vehicle1_info` AFTER INSERT ON `vehicle3`
FOR EACH ROW insert into vehicle(categ,year,make,model,vin,user_id,principal_driver) values (3,new.year,new.make,new.model,new.vin,new.user_id,new.principal_driver)诸如此类……
现在的问题是，当我为一辆汽车插入信息时，触发器执行，值被插入到表vehicle中，但是对于vehicle表中的categ字段，总是插入一个0。categ字段的类型为tinyint(1)。
我不明白出了什么问题。帮助?
更新
车辆的模式
CREATE TABLE IF NOT EXISTS `vehicle` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`categ` tinyint(1) NOT NULL,
`year` char(4) NOT NULL,
`make` varchar(30) NOT NULL,
`model` varchar(50) NOT NULL,
`vin` varchar(25) NOT NULL,
`user_id` int(11) NOT NULL,
`principal_driver` int(11) DEFAULT NULL,
`secondary_driver` varchar(30) NOT NULL,
`status` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
KEY `vin` (`vin`,`user_id`)
) ENGINE=InnoDB;回答 2
Stack Overflow用户
发布于 2012-02-14 17:02:34
您的分类被定义为单个位："TINYINT(1)“分配1位来存储整数。因此，您只能在其中存储0或1。(编辑:我对存储分配的看法是错误的，我误解了文档。)但我真的不明白你为什么要“倒着”输入信息。如果你想避免一堆空条目，我会将信息输入到主车辆表中，然后将记录与特定于车辆类别的列链接起来-通常我只是根据信息的类型重新利用空字段来节省空间(如果我不打算通过该信息搜索太多，如果根本没有)，并且只检索我需要的内容。但我不知道你想完成什么，所以我不能肯定。
编辑:它起作用了吗?如果没有，你遇到了什么问题？下面是我可能会做的事情(注意:不完整，也不选中)：
CREATE TABLE IF NOT EXISTS `logistics`.`vehicle` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
`category` TINYINT(4) NOT NULL COMMENT '(4) Allows for 7 vehicle Categories' ,
`v_year` YEAR NOT NULL ,
`v_make` VARCHAR(30) NOT NULL ,
`created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
`modified` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `logistics`.`driver` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
`first_name` VARCHAR(45) NOT NULL ,
`middle_name` VARCHAR(45) NULL COMMENT 'helpful in cases of 2 drivers with the exact same first and last' ,
`sir_name` VARCHAR(45) NOT NULL ,
`suffix_name` VARCHAR(45) NULL COMMENT 'rather than \"pollute\" your sir name with a suffix' ,
`license_num` VARCHAR(45) NOT NULL COMMENT 'Always handy in case of claims, reporting, and checking with the DMV, etc.' ,
`license_expiration` DATE NOT NULL COMMENT 'Allows status of driver\'s license report to be run and alert staff of needed to verify updated license' ,
`license_class` CHAR(1) NULL COMMENT 'From what I know classes are \'A\' through \'D\' and usually a single letter. Helpful if needing to assign drivers to vehicles.' ,
`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`modified` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `logistics`.`driver_vehicle` (
`vehicle_id` INT(11) UNSIGNED NOT NULL ,
`driver_id` INT(11) UNSIGNED NOT NULL ,
`principal_driver` TINYINT(1) NOT NULL DEFAULT 'FALSE' COMMENT 'if not specified it will be assumed the driver is not a primary.' ,
`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`modified` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ,
`admin_id` INT(11) UNSIGNED NOT NULL ,
PRIMARY KEY (`vehicle_id`, `driver_id`) ,
INDEX `fk_driver_vehicle_driver1` (`driver_id` ASC) ,
CONSTRAINT `fk_driver_vehicle_vehicle`
FOREIGN KEY (`vehicle_id` )
REFERENCES `mydb`.`vehicle` (`id` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_driver_vehicle_driver1`
FOREIGN KEY (`driver_id` )
REFERENCES `mydb`.`driver` (`id` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `logistics`.`vehicle_options` (
`vehicle_id` INT(11) UNSIGNED NOT NULL ,
`option_type` VARCHAR(45) NOT NULL COMMENT 'if certain options are common you could pull by type of option i.e. cosmetic, cargo, hp, weight_capacity, max_speed, etc.' ,
`option_value` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`vehicle_id`, `option_type`) ,
CONSTRAINT `fk_vehicle_options_vehicle1`
FOREIGN KEY (`vehicle_id` )
REFERENCES `mydb`.`vehicle` (`id` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;Stack Overflow用户
发布于 2012-02-14 16:17:25
您的代码示例中是否有触发器具有相同的名称？我建议检查原始代码中的拼写错误。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/9273159
复制相关文章
相似问题
腾讯云开发者
