How to use common_schema inside mysql trigger?

Question

I have to manipulate json object inside mysql trigger. For this I have already installed common_schema.

It is installed successfully and whlie quering directly it is proving approptiate results. However while using same query inside mysql trigger it is throwing error. ERROR 1327 (42000): Undeclared variable: common_schema.

Please assist.

Below is query using mysql common_schema::

  SELECT common_schema.extract_json_value(api.response,'/GUID') AS GUID,
       common_schema.extract_json_value(api.response,'/SUBMITDATE') AS SUBMITDATE,
       common_schema.extract_json_value(api.response,'/ID') AS subscriber_id
FROM api_response api
ORDER BY id DESC LIMIT 10;

+--------------------------------------+---------------------+---------------+
| GUID                                 | SUBMITDATE          | subscriber_id |
+--------------------------------------+---------------------+---------------+
| kf73e550098321f4610003o61mKELLTON2A2 | 2015-07-03 14:55:00 | 173           |
| kf73e550097721f461000cofneKELLTON2A2 | 2015-07-03 14:55:00 | 178           |
| kf73e550096421f461000iqhtfKELLTON2A2 | 2015-07-03 14:55:00 | 175           |
| kf73e550095121f4610008kjwiKELLTON2A2 | 2015-07-03 14:55:00 | 176           |
| kf73e550093621f461000rxq1lKELLTON2A2 | 2015-07-03 14:55:00 | 174           |
| kf73e550092421f461000zh2njKELLTON2A2 | 2015-07-03 14:55:00 | 177           |
| kf73e550091021f461000csbi4KELLTON2A2 | 2015-07-03 14:55:00 | 122           |
| kf73e550089621f461000vqvrdKELLTON2A2 | 2015-07-03 14:55:00 | 121           |
| kf73e550088321f461000sy3dyKELLTON2A2 | 2015-07-03 14:55:00 | 105           |
| kf73e550086821f461000x6bqoKELLTON2A2 | 2015-07-03 14:55:00 | 109           |
+--------------------------------------+---------------------+---------------+

10 rows in set (0.06 sec)

Here is my trigger::

`CREATE TRIGGER response_in_sms_reports AFTER INSERT ON api_response FOR EACH ROW

BEGIN

-- variable declarations DECLARE subscriber_id INT; DECLARE GUID varchar(250); DECLARE SUBMITDATE datetime; DECLARE is_error INT DEFAULT 1; DECLARE error_code varchar(100); -- trigger code -- Find username of person performing the INSERT into table select common_schema.extract_json_value(api.response,'/ID') into subscriber_id, common_schema.extract_json_value(api.response,'/GUID') into GUID, common_schema.extract_json_value(api.response,'/SUBMITDATE') into SUBMITDATE, common_schema.extract_json_value(api.response,'/ERROR') into is_error, common_schema.extract_json_value(api.response,'/ERROR/CODE') into error_code from api_response api

-- Insert record into audit table INSERT INTO sms_reports (guid, list_subscriber_id, submit_date, error, error_code) VALUES (GUID, subscriber_id, SUBMITDATE , is_error, error_code );

END;`

This results in ERROR 1327 (42000): Undeclared variable: common_schema

wchiquito wchiquito · Accepted Answer · 2015-07-15T10:22:29

In addition to the suggested change, avoid naming variables as columns of your tables.

DELIMITER //

CREATE TRIGGER response_in_sms_reports AFTER INSERT ON api_response
FOR EACH ROW
BEGIN
    -- variable declarations
    DECLARE subscriber_id INT;
    DECLARE GUID varchar(250);
    DECLARE SUBMITDATE datetime;
    DECLARE is_error INT DEFAULT 1;
    DECLARE error_code varchar(100);

    -- trigger code
    -- Find username of person performing the INSERT into table

    /*
    select
        common_schema.extract_json_value(api.response,'/ID') into subscriber_id,
        common_schema.extract_json_value(api.response,'/GUID') into GUID,
        common_schema.extract_json_value(api.response,'/SUBMITDATE') into SUBMITDATE,
        common_schema.extract_json_value(api.response,'/ERROR') into is_error,
        common_schema.extract_json_value(api.response,'/ERROR/CODE') into error_code
    from api_response api
    */

    SELECT
        common_schema.extract_json_value(api.response,'/ID'),
        common_schema.extract_json_value(api.response,'/GUID'),
        common_schema.extract_json_value(api.response,'/SUBMITDATE'),
        common_schema.extract_json_value(api.response,'/ERROR'),
        common_schema.extract_json_value(api.response,'/ERROR/CODE')
        INTO subscriber_id, GUID, SUBMITDATE, is_error, error_code
    FROM api_response api;

    -- Insert record into audit table
    INSERT INTO sms_reports (guid, list_subscriber_id, submit_date, error, error_code)
    VALUES
    (GUID, subscriber_id, SUBMITDATE , is_error, error_code );
END//

DELIMITER ;

How to use common_schema inside mysql trigger?

1 Answers