Need an alternative to prepared statements or triggers

[Andy-H]

I have this a problem procedure,

 

 

DROP PROCEDURE IF EXISTS addAlerts;
DELIMITER $$
CREATE PROCEDURE addAlerts(
   IN lID    INT,
   IN lat    DOUBLE,
   IN lon    DOUBLE,
   IN date   VARCHAR(15),
   IN time   VARCHAR(10),
   IN device VARCHAR(13)
)
MODIFIES SQL DATA
rules: BEGIN
   DECLARE rID   INT;
   DECLARE rName VARCHAR(255);
   
   DECLARE rules_broken BOOLEAN;
   DECLARE no_more_rows BOOLEAN;


   DECLARE trig_cur CURSOR FOR
      SELECT r.ruleID, r.rule_name FROM 
      trackers t
      INNER JOIN ( rules r, rulesP rp  )                            ON    ( t.id = rp.trackerID AND rp.ruleID = r.ruleID )
      INNER JOIN ( rules_subrules rsub )                            ON    ( r.ruleID = rsub.ruleID )
      INNER JOIN ( rules_schedule rs   )                            ON    ( r.ruleID = rs.ruleID )
      LEFT JOIN  ( rules_locations rl, rules_locations_links rls )  ON    ( rls.locationID = rl.locationID AND r.ruleID = rls.ruleID )
         WHERE (POW(2, IF(DATE_FORMAT(STR_TO_DATE(date, '%d/%m/%Y'), '%w')=0,7,DATE_FORMAT(STR_TO_DATE(date, '%d/%m/%Y'), '%w'))-1) & rs.schedule_days)
         AND IF(rs.schedule_inorout, IF(time BETWEEN rs.schedule_start AND rs.schedule_end, 1, 0), IF(time NOT BETWEEN rs.schedule_start AND rs.schedule_end, 1, 0))
         AND IF(rl.location_lat IS NOT NULL, GEOFENCE(lat, lon, rl.location_lat, rl.location_lon, rl.location_radius), 1)
         AND t.unitid = device AND r.rule_enabled = 1;


   DECLARE CONTINUE HANDLER FOR NOT FOUND
      SET no_more_rows = TRUE;


   OPEN trig_cur;
   rules_loop : LOOP
      FETCH  trig_cur
      INTO   rID,
             rName
      ;
      SET rules_broken = TRUE;
      
      IF no_more_rows THEN
         CLOSE trig_cur;
         LEAVE rules_loop;
      END IF;
      
      sub_rules: BEGIN
         DECLARE field              VARCHAR(255);
         DECLARE operator           VARCHAR(2);
         DECLARE value              VARCHAR(50);
         
         DECLARE no_more_rows_again BOOLEAN;
         
         DECLARE subrule_cur CURSOR FOR 
            SELECT rule_field, rule_operator, rule_value FROM rules_subrules WHERE ruleID = rID;
         DECLARE CONTINUE HANDLER FOR NOT FOUND
            SET no_more_rows_again = TRUE;
         
         OPEN subrule_cur;
         subrules_loop : LOOP
            FETCH subrule_cur
            INTO  field, 
                  operator, 
                  value
            ;
            IF no_more_rows_again THEN
               CLOSE subrule_cur;
               LEAVE subrules_loop;
            END IF;
            
            SET @field    = field;
            SET @operator = operator;
            SET @value    = value;
            SET @lID      = lID;
            SET @query    = CONCAT('SELECT IF(',@field,@operator,@value, ',1, 0) INTO @broken FROM newlocations WHERE id = ', @lID, ' LIMIT 1');
            PREPARE    stmt FROM @query;
            EXECUTE    stmt;
            DEALLOCATE PREPARE stmt;
            
            IF ( @broken = 0 ) THEN
               SET rules_broken = FALSE;
               CLOSE subrule_cur;
               LEAVE subrules_loop;
            END IF;
         END LOOP subrules_loop;
      END sub_rules;
      
      IF ( rules_broken = TRUE ) THEN
         INSERT INTO alerts ( alertID, locationID, ruleID ) VALUES ( NULL, lID, rID );
      END IF;
   END LOOP rules_loop;
END rules$$
DELIMITER ;

 

 

In particular, this part

DECLARE subrule_cur CURSOR FOR 
            SELECT rule_field, rule_operator, rule_value FROM rules_subrules WHERE ruleID = rID;
         DECLARE CONTINUE HANDLER FOR NOT FOUND
            SET no_more_rows_again = TRUE;
         
         OPEN subrule_cur;
         subrules_loop : LOOP
            FETCH subrule_cur
            INTO  field, 
                  operator, 
                  value
            ;
            IF no_more_rows_again THEN
               CLOSE subrule_cur;
               LEAVE subrules_loop;
            END IF;
            
            SET @field    = field;
            SET @operator = operator;
            SET @value    = value;
            SET @lID      = lID;
            SET @query    = CONCAT('SELECT IF(',@field,@operator,@value, ',1, 0) INTO @broken FROM newlocations WHERE id = ', @lID, ' LIMIT 1');

 

 

I need to call this procedure from an insert trigger, however, when I try to trigger an insert I get:

 

 

#1336 - Dynamic SQL is not allowed in stored function or trigger

Is there any alternative to MySQL triggers/prepared statements? Or a way to enable procedures which contain prepared statements to be called from triggers?

[Andy-H]

Used Nested case instead.