DDL Trigger in MySQL?

Question

Are there any options for running DDL triggers in a MySQL database. I would like to create a view every time someone create a table.

If the "Users" table is created, I want the DDL trigger to create a view called "v_Users".

Answer 1

MySQL only supports triggers for BEFORE / AFTER INSERT, UPDATE, DELETE, see documentation for the CREATE TRIGGER syntax.

An alternative solution might be to create a MySQL EVENT that runs at at a specified interval and checks whether there are any tables that don't have corresponding views, and if so, create them. (Make sure to enable the event scheduler. The default is OFF until MySQL 8.0. See documentation here.)

Maybe another option would be to create the view "if not exists" whenever a row of data is INSERTed into the table, in which case you could use a BEFORE INSERT trigger. (One obvious disadvantage of this approach is that the trigger will be fired every time you INSERT to the table, so there is a performance penalty on INSERT.)

Edit: Maybe yet another option could be that you only allow CREATE TABLE through a special stored procedure which will automatically also create the view.

Answer 2

Thx :) Then i will look for an alternative

Because you will look for an alternative, let me suggest PostgreSQL. It does everything MySQL does, except better. Except lose your data, it tries not to do that at all. For comparison PostgreSQL supports exactly what you want with triggers on DDL using the "Event Trigger" functionality -- an extension of the spec

Event Triggers

For more information see PostgreSQL: Event Triggers. Here is an example if what they look like,

CREATE FUNCTION trigf()
RETURNS event_trigger
AS $$
  BEGIN
    IF (SELECT object_identity = 'public.users'
      FROM pg_event_trigger_ddl_commands())
    THEN
      CREATE VIEW v_Users AS TABLE users;
    END IF;
  END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER foo ON ddl_command_end
  WHEN tag IN ( 'CREATE TABLE' )
  EXECUTE PROCEDURE trigf();

CREATE TABLE users (bleh int DEFAULT 9);

INSERT INTO users VALUES (5), (DEFAULT);
INSERT 0 2
test=# SELECT * FROM v_users ;
 bleh 
-------
     5
     9

---

Microsoft SQL Server, a substantially more expensive closed-source & proprietary option with less functionality supports this with their ddl-trigger's