3

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?

  • 1
    Do you really need it to be concurrent with the insert? What if the trigger just inserted a record into a monitor table. Then you had a stored procedure that runs every X minutes check the monitor table, check if an alarm should be issued and then have it take any appropriate action. Have it clear entries from the monitor table after it handles them. – Jonathan Fite May 3 '17 at 15:22
  • I do need it to be concurrent with the insert. New values will be inserted in matter of seconds, having a stored procedure run in a non concurrent manner every x time would hinder the alarm watching section of my application. The only workaround I can think about right now is replacing the Dynamic SQL bit with a huge CASE block... but I'll only leave it as a last resort, as it is a horrible solution – minusnine May 3 '17 at 17:08
2

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'.)
  • Could you please elaborate on "Plan A"?. I don't understand what you mean by "Replacing the INSERT". The function names stored may not be renamed, but new ones will be added over time, so "spelling them out" means creating a really big "case" block that needs to be edited everytime a new one is added. That would be a pain to maintain. – minusnine May 3 '17 at 18:02
  • 1
    Currently your code has a INSERT somewhere. You want it to call a trigger. Instead of doing INSERT, do CALL to the combined PROCEDURE (INSERT + Loop doing the other stuff). – Rick James May 3 '17 at 18:56
  • Clever idea. That should get the job done, even though it's not a really "clean" solution, e.g: I would need to document that anyone who wants to write in that table should never use a regular INSERT. It's kind of counterintuitive, but it works – minusnine May 3 '17 at 19:28
  • 1
    Maybe you can 'have your cake and eat it, too' -- see my my update. – Rick James May 3 '17 at 20:32

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.