Dynamic SQL is not allowed in stored function or trigger issue while create trigger on mysql

Question

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 ;

What are you trying to accomplish in this trigger? Why are you constructing a dynamic SQL statement? (This is rather unusual and risky!) — user149341
– user149341, Commented Apr 27, 2017 at 7:21
That really doesn't explain what you're trying to do. Can you give a more representative example? (Surely your trigger isn't inserting the same data every time it's activated…) — user149341
– user149341, Commented Apr 27, 2017 at 7:27
SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be used in stored procedures, but not stored functions or triggers.. See C.1 Restrictions on Stored Programs. — wchiquito
– wchiquito, Commented Apr 27, 2017 at 9:12

Sagar Gangwal · Accepted Answer · 2017-04-27 07:10:46Z

3

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.

answered Apr 27, 2017 at 7:10

Sagar Gangwal

8,1303 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

wchiquito Over a year ago

While you can create a prepared statement in a stored procedure, in the context of a trigger, you can't call a stored procedure that executes a prepared statement, the error indicated in the question will be thrown.

Sagar Gangwal Over a year ago

@wchiquito i had tried same.It's working for my end.

wchiquito Over a year ago

Does your stored procedure (with the prepared statement) run correctly from the trigger?.

Sagar Gangwal Over a year ago

@wchiquito Yes it it.

wchiquito Over a year ago

What version of MySQL are you using?.

|

Collectives™ on Stack Overflow

Dynamic SQL is not allowed in stored function or trigger issue while create trigger on mysql

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related