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

  • 2
    MySQL doesn't support them – Philᵀᴹ Jun 19 '18 at 10:03
  • 1
    @sv88erik - And yet you are willing to let users execute CREATE TABLE?? – Rick James Jun 21 '18 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? – a_horse_with_no_name Jun 22 '18 at 9:55
  • 1
    @a_horse_with_no_name: No, I have row permission in the views and table tiggers – eriksv88 Jun 22 '18 at 11:31
  • 1
    How do you get those "row permissions" into the view? – a_horse_with_no_name Jun 22 '18 at 11:32
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.

| improve this answer | |
  • 1
    @sv88erik I've just added yet another option to my answer. – dbdemon Jun 20 '18 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 '18 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

| improve this answer | |
  • To complete the "alternatives": Oracle and Firebird 3.0 also support DDL triggers – a_horse_with_no_name Jun 22 '18 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 '18 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.