mysql - 使用带有存储过程值的触发器将值插入表中

Question

我是新手。因此，如果我的代码或思维方式可以得到改进，请随时纠正我。

我在 MySQL Workbench 和 MySQL 8.0 命令行客户端工作。我有一个名为 ds_testdb2 的数据库及其表结构如下:

ds_testdb2

-表格:

1. Assets 传感器表

2. 设备表

3. mapped_readings_table
4. 读数表
5. 传感器表

每次在我的 readings_table 中插入一条记录时，我都需要执行以下 SELECT 语句(插入 readings_table 大约每 1 分钟发生一次)。此 SELECT 语句连接 4 个表并生成设备的 imei 与其他设备属性(例如 device_id、subscription_id 等来自另一个名为 agiletest 的数据库)的映射。此 SELECT 语句工作正常。

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; 

因此，我将此 SELECT 语句放入存储过程中。此 SP 也能正常工作。

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  $$ 

然后我运行存储过程:

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

现在，每次在我的 readings_table 中插入一条记录时，都需要执行此 SP。所以，我创建了一个触发器，上面写着:

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) 

现在，为了检查触发器的执行情况，我在 MySQLWorkbench 中执行以下步骤:

--第一步，在readings_table中插入1条记录

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. 执行触发器
-已经在 MySQL 命令提示符中运行了它
-响应是:查询正常，0 行受影响(0.04 秒)

第 3 步。检查 mapped_readings_table 是否已更新(当前 #rows= 16)

select * from mapped_readings_table;   

-仍然坚持步骤 1 中的错误。

我的问题是:在第 1 步中，我如何重写我的触发器，以便每次在我的 readings_table 中插入一条记录时，我的存储过程的结果集都存储在我的 mapped_readings_table 中?而且，我验证触发器执行的步骤(步骤 1、2、3)是否正确？

类似的问题在这里:Update table in a trigger with values from stored procedure

Answer 1

解决方法如下:我上面的触发器工作得很好，是完成我想要的工作的正确触发器。问题是我创建了许多虚拟触发器来查看我的触发器是否正常工作。

因此，在 readings_table 上插入之后，执行的是这些旧的虚拟触发器，而不是最后一个。一旦我删除了旧触发器并且我的系统中只有正确的触发器，问题就解决了。