mysql - 通过乘以两列来在插入之前触发更新值

Question

我之前必须用触发器修改插入数据的值。这是我的触发器:

DROP TRIGGER IF EXISTS `Calcul_conso`;CREATE DEFINER=`root`@`localhost` TRIGGER `Calcul_conso` BEFORE INSERT ON `data` FOR EACH ROW begin 
...
DECLARE multiplicateur FLOAT DEFAULT 1;
...

SELECT multiplicateur, calcul_conso into multiplicateur, c_c from capteur where sensor_number = NEW.sensor_number;

SET NEW.value = NEW.value * multiplicateur;
...
end

我有以下结构:

捕获者

sensor_number (Primaire) varchar(64) 非
nom varchar(150) Oui NULL
乘法器 float 非 1

数据

id (Primaire) int(11) 非
sensor_number varchar(64) 非
值 float Oui NULL
日期日期时间非

我尝试了一个简单的 * 来相乘，但似乎不起作用。

示例:

new.value = 153265000 且乘法器 = 0.001

Answer 1

您的 select 语句看起来错误，您不应声明与列名称同名的变量。

DROP TRIGGER IF EXISTS `Calcul_conso`;
delimiter $$
CREATE TRIGGER `Calcul_conso` BEFORE INSERT ON t 
FOR EACH ROW 
begin 

DECLARE vmultiplicateur FLOAT DEFAULT 1;

SELECT multiplicateur into vmultiplicateur from capteur where sensor_number = NEW.sensor_number;

SET NEW.value = NEW.value * vmultiplicateur;
end $$

delimiter ;

如此给出

drop table if exists capteur;
create table capteur
(sensor_number  varchar(64) ,
nom varchar(150) ,
multiplicateur float default 1
)
;

drop table if exists t;
create table t
(id int(11) ,
sensor_number varchar(64),
value float ,
date datetime);

insert into capteur values (1,'aaa',2);

insert into t values (1,1,10,'2018-01-01');

结果

ariaDB [sandbox]> select * from t;
+------+---------------+-------+---------------------+
| id   | sensor_number | value | date                |
+------+---------------+-------+---------------------+
|    1 | 1             |    20 | 2018-01-01 00:00:00 |
+------+---------------+-------+---------------------+
1 row in set (0.00 sec)