CODEWITHSUNDEEP
mysqlsqltriggers
Lollipop
Lollipop

Reputation: 101

Trigger updating value before insert by multicating two columns

I have to modify value of an inserted data with a trigger before. Here is my trigger :

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

I have the following structure :

capteur

sensor_number (Primaire) varchar(64) Non
nom varchar(150) Oui NULL
multiplicateur float Non 1

data

id (Primaire) int(11) Non
sensor_number varchar(64) Non
value float Oui NULL
date datetime Non

I tried a simple * to multiply but it seems not working.

Sample :

new.value = 153265000 and multiplicateur = 0.001

Upvotes: 0

Views: 38

Answers (1)

P.Salmon
P.Salmon

Reputation: 17655

Your select statement looks wrong and you should not declare variables with the same name as column names.

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 ;

so given 

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

Result

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)

Upvotes: 1

Related Questions