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".
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
MySQL only supports triggers for
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
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.
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
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