Description:
This WorkLog proposes adding DDL triggers to MySQL.
DDL triggers are database triggers that execute automatically when a DDL statement is executed. Unlike existing MySQL triggers, which fire on table-level DML events such as INSERT, UPDATE, and DELETE, DDL triggers would fire on schema/object changes such as CREATE, ALTER, DROP and RENAME.
The feature would allow users to audit, restrict, validate, or react to schema changes directly inside MySQL. Database administrators and application teams often need to know when schema changes happen, who performed them, and what object was affected.
The feature should allow users to:
1. Create triggers for DDL events.
2. Execute trigger logic before or after a DDL statement.
3. Audit schema changes into user-defined tables.
4. Block risky DDL operations.
Similar feature is already available in the PostgreSQL, Oracle, and SQL Server.
Use Case 1: Audit all schema changes
A DBA wants to record every schema change in an audit table.
CREATE TABLE admin.ddl_audit_log (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_name VARCHAR(255),
event_name VARCHAR(64),
object_schema VARCHAR(255),
object_name VARCHAR(255),
object_type VARCHAR(64),
event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TRIGGER audit_schema_changes
AFTER DDL ON DATABASE
FOR EACH STATEMENT
BEGIN
INSERT INTO admin.ddl_audit_log (...);
END;
Benefit:
Provides native schema-change visibility without external log parsing.
Use Case 2: Block accidental DROP operations
A production schema should not allow direct DROP operations.
CREATE TRIGGER prevent_drop_in_prod
BEFORE DROP ON SCHEMA
FOR EACH STATEMENT
BEGIN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'DROP operations are not allowed in production schema';
END;
Benefit:
Prevents accidental or unauthorized object deletion.
How to repeat:
NA
Suggested fix:
NA
Description: This WorkLog proposes adding DDL triggers to MySQL. DDL triggers are database triggers that execute automatically when a DDL statement is executed. Unlike existing MySQL triggers, which fire on table-level DML events such as INSERT, UPDATE, and DELETE, DDL triggers would fire on schema/object changes such as CREATE, ALTER, DROP and RENAME. The feature would allow users to audit, restrict, validate, or react to schema changes directly inside MySQL. Database administrators and application teams often need to know when schema changes happen, who performed them, and what object was affected. The feature should allow users to: 1. Create triggers for DDL events. 2. Execute trigger logic before or after a DDL statement. 3. Audit schema changes into user-defined tables. 4. Block risky DDL operations. Similar feature is already available in the PostgreSQL, Oracle, and SQL Server. Use Case 1: Audit all schema changes A DBA wants to record every schema change in an audit table. CREATE TABLE admin.ddl_audit_log ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_name VARCHAR(255), event_name VARCHAR(64), object_schema VARCHAR(255), object_name VARCHAR(255), object_type VARCHAR(64), event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TRIGGER audit_schema_changes AFTER DDL ON DATABASE FOR EACH STATEMENT BEGIN INSERT INTO admin.ddl_audit_log (...); END; Benefit: Provides native schema-change visibility without external log parsing. Use Case 2: Block accidental DROP operations A production schema should not allow direct DROP operations. CREATE TRIGGER prevent_drop_in_prod BEFORE DROP ON SCHEMA FOR EACH STATEMENT BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'DROP operations are not allowed in production schema'; END; Benefit: Prevents accidental or unauthorized object deletion. How to repeat: NA Suggested fix: NA