3

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".

15
  • 2
    MySQL doesn't support them
    – Philᵀᴹ
    Jun 19, 2018 at 10:03
  • 1
    @sv88erik - And yet you are willing to let users execute CREATE TABLE??
    – Rick James
    Jun 21, 2018 at 17:32
  • 1
    I don't understand this. If the users aren't allowed to create the tables, then only giving them select privileges on the tables would serve the same purpose, wouldn't it? Jun 22, 2018 at 9:55
  • 1
    @a_horse_with_no_name: No, I have row permission in the views and table tiggers
    – eriksv88
    Jun 22, 2018 at 11:31
  • 1
    How do you get those "row permissions" into the view? Jun 22, 2018 at 11:32

2 Answers 2

2

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.

2
  • 1
    @sv88erik I've just added yet another option to my answer.
    – dbdemon
    Jun 20, 2018 at 9:40
  • I do not really like the last solution. Likes that things are relational so that no matter how to create a table, it will always be a view for it
    – eriksv88
    Jun 20, 2018 at 10:44
1

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

2
  • To complete the "alternatives": Oracle and Firebird 3.0 also support DDL triggers Jun 22, 2018 at 9:49
  • Thank you very much for the option. But unfortunately, it's not my decision in the choice of technology. If I had chosen I had chosen SQL Sever. So what I put in alternative is another way to solve the problem. Anyway +1! :)
    – eriksv88
    Jun 22, 2018 at 11:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.