0

I'm a newbie here. So, please feel free to correct me if my code or thinking approach can be worked upon.

I am working in MySQL Workbench and MySQL 8.0 Command Line Client. I have a database called ds_testdb2 with its tables structured as follows:

ds_testdb2

-Tables:

  1. asset_sensor_table

  2. device_table

  3. mapped_readings_table
  4. readings_table
  5. sensor_table

I need to execute the following SELECT statement every time a record is inserted in my readings_table (insertion into the readings_table happens approx. every 1 minute). This SELECT statement joins 4 tables and produces a mapping of the device's imei with other device attributes such as device_id, subscription_id,etc from another database named agiletest. This SELECT statement works correctly.

SELECT r.imei, r.send_time, r.latitude, r.longitude, r.temperature, r.ground_velocity,
        d.sys_id  AS device_id, d.model_id, d.Subscription_ID,
        s.id AS sensor_id, s.type_id AS sensor_type,
        a_s.asset_id, a_s.start_time, a_s.end_time
        FROM 
        ds_testdb2.readings_table r JOIN agiletest.device d ON  r.imei= d.imei
        JOIN agiletest.sensor s ON d.sys_id= s.device_id 
        JOIN agiletest.asset_sensor a_s ON s.id= a_s.sensor_id; 

So, I put this SELECT statement in a Stored Procedure. This SP works correctly as well.

DELIMITER $$
CREATE PROCEDURE `ds_testdb2`.`mapping_2`()
    BEGIN
        INSERT INTO `ds_testdb2`.`mapped_readings_table`  -- mapped_readings-table already existed, but was empty.
        (`imei`, `send_time`, `latitude`, `longitude`,`temperature`,`ground_velocity`,
        `device_id`,`model_id`,`subscription_id`,
        `sensor_id`,`sensor_type`,
        `asset_id`,`start_time`,`end_time`) -- no 'VALUES' keyword      
        SELECT r.imei, r.send_time, r.latitude, r.longitude, r.temperature, r.ground_velocity,
        d.sys_id  AS device_id, d.model_id, d.Subscription_ID,
        s.id AS sensor_id, s.type_id AS sensor_type,
        a_s.asset_id, a_s.start_time, a_s.end_time
        FROM 
        ds_testdb2.readings_table r JOIN agiletest.device d ON  r.imei= d.imei
        JOIN agiletest.sensor s ON d.sys_id= s.device_id 
        JOIN agiletest.asset_sensor a_s ON s.id= a_s.sensor_id;    
    END  $$ 

I then run the Stored Procedure:

mysql> call mapping_2;
Query OK, 4 rows affected (0.02 sec)

Now, this SP needs to be executed every time a record is inserted in my readings_table. So, I created a trigger that says:

mysql> DELIMITER $$
mysql> CREATE TRIGGER initial_table_mapping_1 AFTER INSERT ON readings_table
    -> FOR EACH ROW
    -> BEGIN
    -> call mapping_2();
    -> END $$
Query OK, 0 rows affected (0.04 sec) 

Now, To check the execution of the trigger, I am doing the following steps in MySQLWorkbench:

--Step 1. Insert 1 record into the readings_table

INSERT INTO readings_table -- (imei,send_time, latitude, longitude, temperature, ground_velocity)
VALUES
(354721090118251, '2019-04-07 21:26:48',43.63218, -79.51890, 108, 50);

Error Code: 1415. Not allowed to return a result set from a trigger

--Step 2. execute the trigger
-already ran it in the MySQL Command Prompt
-Response was: Query OK, 0 rows affected (0.04 sec)

Step 3. check if the mapped_readings_table was updated(current #rows= 16)

select * from mapped_readings_table;   

-still stuck with the Error from Step1.

My Question is: In Step1, how can I rewrite my trigger such that- the result set from my Stored Procedure gets stored in my mapped_readings_table, everytime a record is inserted in my readings_table? And, are my steps (Step 1,2,3) to verify the execution of my trigger, correct?

A similar question is here: https://stackoverflow.com/questions/41624559/update-table-in-a-trigger-with-values-from-stored-procedure

  • Did you try the answer from the page you linked at the end and change it from an after insert to before insert? – raphael75 May 10 at 12:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.