Mysql – How to trigger a function with DDL query

ddlddl-triggerfunctionsMySQL

I want to add a few sanity checks on my DDL statements. Unfortunately, I cannot run a validation separately due to some constraints. I am thinking to run validation inside the DB by calling a stored function like this:

DELIMITER $$

CREATE DEFINER='root'@'localhost' FUNCTION checksql (
       stmt TEXT
   )
   returns int
BEGIN

   if locate("create", stmt) = 1 then
       if locate("primary key", stmt) = 0 then
           SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No Primary key defined';
       end if;
   else
       SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'Unknown SQL statement';
   end if;
   return 1;
END$$
DELIMITER ;

but I could not find a way to trigger a function whenever a DDL get executed.

One way is:

START TRANSACTION;
SET autocommit=0;
SELECT checksql("CREATE TABLE ...");
CREATE TABLE ...;
COMMIT;

But this forces me to make the changes in my code. Is there any methods that i can use to trigger the function without making any code changes?

Best Answer

DDL statements terminate transactions. So your attempt with START TRANSACTION; ... COMMIT; does not work. (MySQL 8.0 may change that.)

If you are just checking for the existence of a PRIMARY KEY, suggest this (in a Stored Procedure) (and with a pre-created DATABASE test_area):

USE test_area;
CREATE TABLE ...  -- probably need prepare+execute+deallocate_prepare
abort if errors
IF ( ( SELECT ... FROM information_schema ...
    WHERE table_schema = 'test_area' ) )
THEN
    issue error message
END IF;
DROP TABLE ...;  -- probably need prepare+execute+deallocate_prepare
Related Question