mysql trigger partially working

Question

My application requires 5 different categories of vehicles and each category of vehicle has some common fields. So what i did was created 5 tables for each of the 5 categories of vehicles vehicle1, vehicle2,vehicle3,vehicle4,vehicle5 and then created a 6th table 'vehicle' for storing fields which are common to each vehicle. Now whenever i enter info related to a particular vehicle which is an INSERT INTO that particular vehicle's category table, a trigger executes that inserts the common fields into the vehicle table. so the triggers look like this

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)

and so on .....

Now the problem is that when i insert info for a vehicle the trigger executes and the values are inserted in the table vehicle but for the categ field in the vehicle table always a 0 is inserted. the type of the categ field is tinyint(1).

I dont understand whats wrong. Help?

UPDATE

schema of vehicle

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;

Answer 1

Your categ is defined as a single bit: "TINYINT(1)" allocates 1 bit to store the integer. So you can only store a 0 or a 1 in it. (EDIT: I am wrong about storage allocation, I misunderstood the documentation.) But I honestly don't understand why you're entering info "backwards." I would enter info into the master vehicle table and then link the records to break out tables with columns specific to the vehicle category if you want to avoid a bunch of null entries — usually I just repurpose the empty fields based on the type of info to save space (if I'm not going to search via that info much, if at all) and only retrieve what I need. But I don't know what you're trying to accomplish, so I can't say for certain.

EDIT: Did it work, and if not what problems are you having? Here is what I would probably do (note: not complete, nor checked):

        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;

Answer 2

Is it correct that any of the triggers in your code sample have the same name? I would suggest to check for typos in the original code.