MySQL trigger is preventing INSERT if condition isn't met

问题: I have a trigger before insert that replaces a string for a field with the new insert id. This is wrapped in a condition and works very well when the condition is met. Howe...

问题:

I have a trigger before insert that replaces a string for a field with the new insert id. This is wrapped in a condition and works very well when the condition is met. However when an ordinary insert is attempted and the string isn't present the trigger stops the insert from executing at all, and yet there is nothing in the trigger to do this, any clues why this might be happening? Trigger below:

DELIMITER $$

DROP TRIGGER /*!50032 IF EXISTS */ `delivery_management_vehicle_placeholder_title`$$

CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `delivery_management_vehicle_placeholder_title` BEFORE INSERT ON `delivery_management_vehicle` 
FOR EACH ROW BEGIN
    DECLARE id_delivery_management_vehicle INT DEFAULT 0;
    DECLARE mask VARCHAR(10) DEFAULT '--id--';
    IF LOCATE(mask, NEW.`title`) > 0 THEN
       SELECT 
           `auto_increment` INTO id_delivery_management_vehicle
       FROM
           `information_schema`.`tables`
       WHERE 
           `table_name` = 'delivery_management_vehicle'
       AND 
           `table_schema` = DATABASE();

       SET NEW.`title`=REPLACE(NEW.`title`, mask, id_delivery_management_vehicle);
   END IF;
 END;
$$

DELIMITER ;

The table structure is as follows:

CREATE TABLE `delivery_management_vehicle` (
  `id_dmv` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `id_system_user` int(11) NOT NULL,
  `id_organisation` int(11) NOT NULL DEFAULT '0',
  `id_depth1` int(11) NOT NULL DEFAULT '0',
  `id_depth2` int(11) NOT NULL DEFAULT '0',
  `id_type` int(11) NOT NULL DEFAULT '1',
  `id_approver` int(11) NOT NULL DEFAULT '0',
  `id_author` int(11) NOT NULL DEFAULT '0',
  `id_owner` int(11) NOT NULL DEFAULT '0',
  `id_responsible` int(11) NOT NULL DEFAULT '0',
  `id_administrator` int(11) NOT NULL DEFAULT '0',
  `id_category` int(11) NOT NULL DEFAULT '1',
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `keywords` text NOT NULL,
  `colour` char(6) DEFAULT NULL,
  `scope` text NOT NULL,
  `assumptions` text NOT NULL,
  `objectives` text NOT NULL,
  `reference` varchar(255) NOT NULL,
  `source` varchar(255) NOT NULL DEFAULT '',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_updated` datetime NOT NULL,
  `last_updated_by` int(11) NOT NULL,
  `approved` enum('0','1') NOT NULL DEFAULT '0',
  `priority` enum('undefined','low','medium','high','critical') NOT NULL DEFAULT 'undefined',
  `approved_date` datetime NOT NULL,
  `actual_start_date` date NOT NULL,
  `planned_start_date` date NOT NULL,
  `projected_start_date` date NOT NULL,
  `planned_completion_date` date NOT NULL,
  `projected_completion_date` date NOT NULL,
  `actual_completion_date` date NOT NULL,
  `tolerance` int(11) NOT NULL,
  `tolerance_period` enum('days','weeks','months') NOT NULL,
  `planned_implementation` bigint(19) DEFAULT NULL,
  `planned_management` bigint(19) DEFAULT NULL,
  `planned_budget` bigint(19) DEFAULT NULL,
  `projected_management` bigint(19) DEFAULT NULL,
  `projected_budget` bigint(19) DEFAULT NULL,
  `actual_implementation` bigint(19) DEFAULT NULL,
  `actual_management` bigint(19) DEFAULT NULL,
  `actual_budget` bigint(19) DEFAULT NULL,
  `financial_summary_tolerance` int(11) DEFAULT '0',
  `use_rag` enum('D','R','A','G','B') NOT NULL DEFAULT 'D',
  `mandatory` enum('0','1') NOT NULL DEFAULT '0',
  `active` enum('0','1') NOT NULL DEFAULT '1',
   PRIMARY KEY (`id_dmv`),
  KEY `id_system_user` (`id_system_user`),
  KEY `id_type` (`id_type`),
  KEY `id_approver` (`id_approver`),
  KEY `id_author` (`id_author`),
  KEY `id_owner` (`id_owner`),
  KEY `id_responsible` (`id_responsible`),
  KEY `id_administrator` (`id_administrator`),
  KEY `id_system_user_2` (`id_system_user`,`active`),
  KEY `id_author_2` (`id_author`,`active`),
  KEY `id_owner_2` (`id_owner`,`active`),
  KEY `id_approver_2` (`id_approver`,`active`),
  KEY `id_responsible_2` (`id_responsible`,`active`),
  KEY `id_type_2` (`id_type`,`active`)
) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='Delivery Management Vehicle';

and the insert statement I am trying to execute is:

INSERT INTO `delivery_management_vehicle` (
  `last_updated`,
  `title`,
  `reference`,
  `description`,
  `keywords`,
  `source`,
  `priority`,
  `scope`,
  `assumptions`,
  `objectives`,
  `use_rag`,
  `id_category`,
  `id_type`,
  `id_organisation`,
  `id_depth1`,
  `id_depth2`,
  `colour`,
  `id_system_user`,
  `actual_start_date`,
  `planned_start_date`,
  `projected_start_date`,
  `planned_completion_date`,
  `projected_completion_date`,
  `tolerance`,
  `tolerance_period`,
  `last_updated_by`
) VALUES (
NOW(),'G Test 3','','','','','undefined','','','','D',1,1,0,0,0,NULL,'3','','','','','',0,'days','3');

Sorry the table declaration is huge, but the insert is only for certain columns.

So looking at the title field I am expecting this to just insert and the trigger bypasses because the string '--id--' does not exist in the title field.

Cheers


回答1:

You are trying to insert '' into an INT column. You can't do that.

  • 发表于 2019-03-16 00:32
  • 阅读 ( 46 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除