i am getting exception while create a trigger on mysql, Pasting piece of my trigger below. I hope the prepare statement in this trigger throws this exception, Please correct me anything error in syntax.
DELIMITER $$
CREATE DEFINER=`root`@`localhost` TRIGGER mysql_common.mysql_alert_trigger after insert on mysql_common.alert FOR EACH ROW
BEGIN
set @col_string = "col1,col2,col3";
set @val_string = "('val1','val2','val3')";
SET @s := CONCAT('INSERT into msql_common.seperated_processlist(',@col_string,') values ', @val_string);
PREPARE dynamic_statement FROM @s;
EXECUTE dynamic_statement;
DEALLOCATE PREPARE dynamic_statement;
END $$
DELIMITER ;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` TRIGGER mysql_common.mysql_alert_trigger after insert on mysql_common.alert FOR EACH ROWEACH ROW
BEGIN
call SOME_PROC();
END $$
DELIMITER ;
You can wrote one procedure with your business logic and call that procedure from trigger as shown in above code.with your specific input and output parameter.
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `SOME_PROC`()
BEGIN
set @col_string = "col1,col2,col3";
set @val_string = "('val1','val2','val3')";
SET @s := CONCAT('INSERT into msql_common.seperated_processlist(',@col_string,') values ', @val_string);
PREPARE dynamic_statement FROM @s;
EXECUTE dynamic_statement;
DEALLOCATE PREPARE dynamic_statement;
END$$
DELIMITER ;
Hope this will helps.