MYSQL触发麻烦 [英] MYSQL trigger trouble
问题描述
我要触发此操作:
UPDATE formulario
SET ano = EXTRACT(year FROM create_date),
mes = EXTRACT(month FROM create_date)
但是我在制造它时遇到了很多麻烦.它被接受但不起作用,或者在发布新行时给出错误消息.我想插入 create_date
并自动安装 ano
和 mes
.我该怎么办?
But i am getting a lot of trouble making it. it is accepted but doesn't work or gives away error message on posting new rows.
I want to insert the create_date
and get the ano
and mes
auto fitted. how can i do this ?
抱歉,这是触发信号的定义:
sorry here is the trigger defenition:
CREATE TRIGGER `anomes` AFTER INSERT ON `formulario` FOR EACH ROW BEGIN
UPDATE formulario SET ano = EXTRACT(year FROM create_date);
UPDATE formulario SET mes = EXTRACT(month FROM create_date);
END;
和表格:
-- Dumping structure for table at.formulario
CREATE TABLE IF NOT EXISTS `formulario` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`uniqid` varchar(38) DEFAULT NULL,
`create_date` date DEFAULT NULL,
`ano` int(4) DEFAULT NULL,
`mes` int(2) DEFAULT NULL,
`codcliente` int(8) DEFAULT NULL,
`tipo_act` int(1) DEFAULT NULL,
`contacto_nome` varchar(50) DEFAULT NULL,
`contacto_funcao` int(1) DEFAULT NULL,
`contacto_telefone` varchar(20) DEFAULT NULL,
`cara_cli_abc` int(1) DEFAULT NULL,
`cara_cli_estado` int(1) DEFAULT NULL,
`cara_cli_tipo_est` int(3) DEFAULT NULL,
`cara_cli_sazonal` int(1) DEFAULT '0',
`loc_inst_piso` varchar(5) DEFAULT NULL,
`loc_inst_acesso` int(1) DEFAULT NULL,
`loc_inst_data_vistoria` date DEFAULT NULL,
`loc_inst_data_inter` date DEFAULT NULL,
`loc_inst_time_vistoria_start` time DEFAULT NULL,
`loc_inst_time_vistoria_end` time DEFAULT NULL,
`loc_inst_time_inter_start` time DEFAULT NULL,
`loc_inst_time_inter_end` time DEFAULT NULL,
`check_list_p_agua` int(1) DEFAULT '0',
`check_list_t_agua` int(1) DEFAULT '0',
`check_list_p_dist` varchar(3) DEFAULT NULL,
`check_list_obs` text,
`final_obs` text,
`vendedor` int(4) DEFAULT NULL,
`dt_aceito` int(1) DEFAULT NULL,
`bonus` text,
`anulado` tinyint(1) DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
推荐答案
问题是您无法修改调用此触发器的表.这是一个限制.
The problem is that you cannot modify the table that invoked this trigger. It is a restriction.
尝试在插入数据之前使用INSERT触发器,并设置新值.例如:
Try to use a BEFORE INSERT trigger and set new values before the inserting data. For example:
CREATE TRIGGER anomes
BEFORE INSERT
ON formulario
FOR EACH ROW
BEGIN
SET NEW.ano = EXTRACT(year FROM NEW.create_date);
SET NEW.mes = EXTRACT(month FROM NEW.create_date);
END
还有一个问题-为什么要将这些数据存储在表中?您可以在SELECT查询中即时计算它.
And the question - why are you going to store this data in the table? You can calculate it on the fly in SELECT query.
这篇关于MYSQL触发麻烦的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!