Untitled

Calling stored procedure that contains dynamic SQL from Trigger

-- Trigger DDL Statements

DELIMITER $$

 

USE `TestaDataBase`$$

CREATE TRIGGER `TestaDataBase`.`UpdateAuxilaryTable`

AFTER INSERT ON `MainTable` FOR EACH ROW  

BEGIN    

    /* Here we call stored procedure with parameter id of newly inserted row. */

    CALL TestProcedure('Year', 'Person', 'IdPerson', NEW.IdData);

END

$$

       

DELIMITER $$

CREATE PROCEDURE `TestDataBase`.`TestProcedure` (IN attribute CHAR(64), IN tableName CHAR(64), IN IdTable CHAR(64), IN IdLastRow MEDIUMINT)

BEGIN

DECLARE selectedValue MEDIUMINT;

 

SET @statement = CONCAT('SELECT ', attribute, ' FROM ', tableName, ' WHERE ', IdTable, ' = ', IdLastRow, ' INTO selectedValue');

PREPARE statementExecute FROM @statement;

EXECUTE statementExecute ;

...

...

END