CODEWITHSUNDEEP
mysqlstored-procedurestriggersmysql-workbench
Sineth Lakshitha
Sineth Lakshitha

Reputation: 713

mysql create trigger inside stored procedure

How can I create a trigger by executing a stored procedure in mysql. If it is not possible please explain the reasons. Is there any way to create triggers dinamicaly without creating each every time?

Upvotes: 0

Views: 582

Answers (1)

Akina
Akina

Reputation: 42704

If it is not possible please explain the reasons.

MySQL 8.0 Reference Manual / Stored Objects / Restrictions on Stored Programs # SQL Statements Not Permitted in Stored Routines

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 13.5, “Prepared Statements”. Exceptions are SIGNAL, RESIGNAL, and GET DIAGNOSTICS, which are not permissible as prepared statements but are permitted in stored programs.

MySQL 8.0 Reference Manual / SQL Statements / Prepared Statements # SQL Syntax Permitted in Prepared Statements

The complete list of the statements allowed is provided. There is no CREATE TRIGGER statement in this list.

Hence, trigger cannot be created in stored procedure.

But you can create a trigger and influence its execution. For example, you can create some service table in the database, and check the values stored in it in the trigger code - use them as parameters or execute/skip various blocks of code.

This corresponds to the principle "client code should not perform DDL operations" also.

Upvotes: 1

Related Questions