mysql参考触发条件_mysql - 如果不满足条件，MySQL触发器会阻止INSERT - SO中文参考 - www.soinside.com...

我在插入之前有一个触发器，用新插入ID替换字段的字符串。这包含在条件中，并且在满足条件时非常有效。但是，当尝试普通插入并且字符串不存在时，触发器根本不会执行插入操作，但触发器中没有任何内容可以执行此操作，任何线索可能会发生这种情况？触发如下：

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 ;

表结构如下：

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';

我试图执行的insert语句是：

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');

抱歉，表声明很大，但插入仅适用于某些列。

所以看标题字段我希望这只是插入和触发器旁路，因为字符串'--id--'在标题字段中不存在。

干杯