Hi,
We are currently migrating from oracle to mariadb. We have around 100 tables , each having date columns and associated before insert triggers to populate current_timestamp into the date columns.
I have written a procedure to generate the trigger DDL , which takes database name and table name as input
Procedure is able to generate the Trigger DDL accurately but is not able to execute the same using prepare statement(Dynamic SQL) as shown below
PREPARE q from v_trigger_ddl;
EXECUTE q;
DEALLOCATE PREPARE q;
But if I execute the same trigger DDL generated by procedure manually on the command prompt, it executes without any errors.
Please help me If I am missing anything , as it will reduce the manual effort if the procedure executes the DDL as well.
You really can't, it's not supported and is not a bug.
MySQL documentation is clearer about that, see
http://dev.mysql.com/doc/refman/5.7/en/stored-program-restrictions.html
Generally, statements not permitted in SQL prepared statements are also not permitted in stored programs. For a list of statements supported as prepared statements, see Section 14.5, “SQL Syntax for Prepared Statements”. Exceptions are SIGNAL, RESIGNAL, and GET DIAGNOSTICS, which are not permissible as prepared statements but are permitted in stored programs.
and
http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html for the list of supported statements.
MariaDB documentation is a little more vague:
https://mariadb.com/kb/en/mariadb/stored-routine-limitations/
The following SQL statements are not permitted inside any stored routines (stored functions, stored procedures, events or triggers).
- Many SQL not permitted in prepared statements.
and https://mariadb.com/kb/en/mariadb/prepare-statement/ for the list of statements supported in PS.
"Many SQL not permitted" is not specific enough, maybe the wording needs to be improved, assigning to greenman for this.
Ok thank you 
Is there any other way to do ....so that we can automate the generation of Trigger DDL
First that comes to mind is to redirect the generated statements to a file, and then feed the resulting SQL file to the client.
Yes I got the same idea...But then I am creating another procedure to which I will input all the table names and call the first procedure in a loop. Now the problem is, I am not able to append all the triggers DDL to the same file using using INTO OUTFILE.
There is a limitation to INTO OUTFILE (It can only open a new file). So is there any other way I can re-route all the DDL by appending to the same file???
This has been clarified in the documentation.