CODEWITHSUNDEEP
mysqltriggers
Miroslav Savel
Miroslav Savel

Reputation: 314

How to SELECT multiple values in MYSQL trigger?

how can I SELECT multiple values INTO variables in MYSQL trigger? I have tried SELECT values into variables in this way but it didn't work. I was inspired by this thread how to use trigger to set value based on query result. When I examined value in variables, it is NULL. When I put this SELECT into mysql workbench it will select right values. I check column types and they are same type as variables in trigger. With debug I discovered that there is problem with SELECTing values. Thank you in advance.

Here is my trigger:

CREATE TRIGGER fin_den_zam_insert
AFTER INSERT
     ON table1 FOR EACH ROW

BEGIN

DECLARE koef1 DECIMAL(6,3);
DECLARE koef2 DECIMAL(6,3);
DECLARE koef3 DECIMAL(6,3);
DECLARE sum DECIMAL(6,3);

SELECT DISTINCT
    koef_salary, koef_sunday, koef_holiday
INTO 
    koef1 , koef2, koef3 
FROM 
    employee E
WHERE 
    E.personal_number = NEW.personal_number_id;


SET sum := NEW.salary * (koef1 + koef2 + koef3);


INSERT INTO export_table
            (
                id_export,
                personal_number, 
                final_sum
                 ) 
VALUES 
            (
                NULL,
                NEW.personal_number_id, 
                sum
                ); 

END; 

//

DELIMITER ;

Upvotes: 3

Views: 2393

Answers (1)

nbk
nbk

Reputation: 49395

You must be careful with reserved words like sum, that can cause very much trouble.

most people write before every own variable _ like _sum, so that also a stranger can identify such variables.

That is as you can see not absolutely necessary, but helps also when you take a look in 5 years

create table employee
(personal_number int,koef_salary DECIMAL(6,3), koef_sunday DECIMAL(6,3), koef_holiday DECIMAL(6,3));

create table export_table(
                id_export int auto_increment primary key,
                personal_number int, 
                final_sum DECIMAL(6,3)
                 );

CREATE table table1 
(id_export int auto_increment primary key
, personal_number_id int
, salary DECIMAL(6,3));

insert into employee values(1,1.1,1.3,1.4);

CREATE TRIGGER fin_den_zam_insert
AFTER INSERT
     ON table1 FOR EACH ROW

BEGIN

DECLARE koef1 DECIMAL(6,3);
DECLARE koef2 DECIMAL(6,3);
DECLARE koef3 DECIMAL(6,3);
DECLARE final_sum DECIMAL(6,3);

SELECT DISTINCT
    koef_salary, koef_sunday, koef_holiday
INTO 
    koef1 , koef2, koef3 
FROM 
    employee E
WHERE 
    E.personal_number = NEW.personal_number_id;


SET final_sum := NEW.salary * (koef1 + koef2 + koef3);


INSERT INTO export_table
            (
                id_export,
                personal_number, 
                final_sum
                 ) 
VALUES 
            (
                NULL,
                NEW.personal_number_id, 
                final_sum
                ); 

END

INSERT INTO table1 VALUES (NULL,1,100)

SELECT * FROM export_table

id_export | personal_number | final_sum
--------: | --------------: | --------:
        1 |               1 |   380.000

db<>fiddle here

Upvotes: 3

Related Questions