Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL trigger to fire on alter or drop

Tags:

mysql

triggers

I've encountered an interesting problem. I would like to create two triggers, trigger1 would fire on DROP TABLE and trigger2 on ALTER TABLE. I would like trigger1 and trigger2 to fire if any table is dropped or altered, but, unfortunately I don't know the syntax to do that and I couldn't find a syntax for such triggers either. In MySQL I can only write triggers which fire before/after INSERT, UPDATE, or DELETE, but now I would like to write triggers which would be applicable on database level and would fire on table events. Can somebody help me?

like image 412
Lajos Arpad Avatar asked Dec 28 '11 14:12

Lajos Arpad


People also ask

Can we alter trigger in MySQL?

To modify an existing trigger, double-click the node of the trigger to modify, or right-click this node and choose the Alter Trigger command from the context menu. Either of the commands opens the SQL Editor.

How does triggers work in MySQL?

A trigger is defined to activate when a statement inserts, updates, or deletes rows in the associated table. These row operations are trigger events. For example, rows can be inserted by INSERT or LOAD DATA statements, and an insert trigger activates for each inserted row.

Does alter table trigger update?

An alter to the table is not an update to the data, so a trigger defined to fire on update will not fire when an alter is executed.

Does trigger improve query performance?

Triggers improve performance the most when they execute more SQL statements and the network speed is comparatively slow. When the database server executes an SQL statement, it must perform the following actions: Determine if triggers must be fired.


2 Answers

What you are looking for is known as "DDL Triggers". MySQL does not support them

There is this SourceForge request but I'm not sure how serious anyone is about adding it

like image 120
gbn Avatar answered Oct 12 '22 23:10

gbn


It's not possible to use a trigger for drop statements.According to the documentation the trigger is activated on

  • INSERT: The trigger is activated whenever a new row is inserted into the table; for example, through INSERT, LOAD DATA, and REPLACE statements.

    UPDATE: The trigger is activated whenever a row is modified; for example, through UPDATE statements.

    DELETE: The trigger is activated whenever a row is deleted from the table; for example, through DELETE and REPLACE statements. However, DROP TABLE and TRUNCATE TABLE statements on the table do not activate this trigger, because they do not use DELETE.

It doesn't say anything about alter table, but I would expect only DML-statements to be able to fire a trigger.

like image 20
John P Avatar answered Oct 13 '22 00:10

John P