Alternative to Dynamic SQL inside trigger?

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;

#**

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'.)

StackExchange Database Administrators Q#172644, answer score: 2