Alternative to Dynamic SQL Inside MySQL Trigger

dynamic-sqlMySQLmysql-workbenchprepared-statementtrigger

My database has the following tables:

    TAG
    ----------------------
    | tag_id | tag_name  |
    ----------------------

    TAG_VALUE: Stores values associated to each tag
    ----------------------------------------
    | tag_id | insertion_timestamp | value |
    ----------------------------------------

    ALARM: Defines alarms for each tag
    -------------------------------------
    | alarm_id | tag_id | function_name |
    -------------------------------------

    ALARM ACTIVATION: Stores information regarding each time the alarms were triggered
    -----------------------------------------------------
    | alarm_id | activation_timestamp | activation_value|
    -----------------------------------------------------

So, everytime a new value is inserted into TAG_VALUE, I need to check if that new value triggers any alarm associated to it's tag. Since there is no common criteria between alarms, I'm just storing a function name, which will be used later on to determine if the alarm should be triggered or not

TAG_VALUE has the following AFTER_INSERT trigger:

CREATE DEFINER=`root`@`localhost` TRIGGER `mydb`.`tag_value_AFTER_INSERT` AFTER INSERT ON `tag_value` FOR EACH ROW
BEGIN
call sp_alarm_handler(NEW.tag_value, NEW.tag_id);
END

And sp_alarm_handler is coded as follows:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_alarm_handler`(IN tag_value VARCHAR(255), IN value_tag_id INT(11))
    BEGIN

    DECLARE exit_loop BOOLEAN;
    DECLARE v_alarm_id INT(11);
    DECLARE function_name VARCHAR(255);
    DECLARE value_triggers_alarm TINYINT(1);

    DECLARE custom_alarm_cur CURSOR FOR
    Select alarm_id, function_name From vw_custom_alarms where tag_id = value_tag_id;


    DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;

    OPEN custom_alarm_cur;

    custom_alarm_lp: LOOP

        FETCH custom_alarm_cur into v_alarm_id, function_name;

        IF exit_loop THEN
        leave custom_alarm_lp;
        END IF;

        #************************************************
        #*********HERE'S THE DYNAMIC SQL PIECE'**********
        #************************************************
        set @query = CONCAT('Select ',function_name,'(',tag_value,')', 'into @value_triggers_alarm');
        PREPARE stmt FROM @query;
        Execute stmt;

        IF fn_is_alarm_active(v_alarm_id) = 0 THEN

            If @value_triggers_alarm = 1 THEN
                    INSERT INTO alarm_activation(alarm_id, activation_timestamp, activation_value)
                    VALUES (v_alarm_id, NOW(), tag_value);
            end if;

        ELSE IF @value_triggers_alarm = 0 THEN
            call sp_deactivate_alarm(v_alarm_id);

            END IF;
        END IF;



    END LOOP custom_alarm_lp;
    Close custom_alarm_cur;

    END

This gives me an ERROR 1336: 1336: Dynamic SQL is not allowed in stored function or trigger
Is there any workaround in my case?

Best Answer

If the list things to check is constant, then spell them out rather than constructing the tests. (not viable)

But, presumably that is not the case...

Plan A: Replace the INSERT that needs to call the trigger with a PROCEDURE call. The routine contains the INSERT plus the rest of the code you presented.

Plan B: As with Plan A, but do it in application code.

Note: Either A or B would be wrapped in BEGIN...COMMIT to make it 'atomic'.

Bottom Line: Triggers can't do everything.

Enforcement of A (I think this will prevent other from bypassing the stored proc.)

  • CREATE USER special@... ... -- a new user
  • GRANT INSERT ON db.tbl TO special@... -- let it get in
  • The stored proc would be SECURITY special@... -- to run proc as "special" so it can do the INSERTs
  • Remove INSERT PRIVILEGE for that table from others. (This gets messy, or maybe unnecessary.) (It may help if the table is in a separate database that no one currently has access to.)
  • (My gut says there is one more step. I'll leave it as a 'exercise for the reader'.)
Related Question