We can drop/delete/remove a trigger in MySQL using the DROP TRIGGER statement. You must be very careful while removing a trigger from the table. Because once we have deleted the trigger, it cannot be recovered. If a trigger is not found, the DROP TRIGGER statement throws an error.
MySQL allows us to drop/delete/remove a trigger mainly in two ways:
- MySQL Command Line Client
- MySQL Workbench
MySQL Command Line Client
We can drop an existing trigger from the database by using the DROP TRIGGER statement with the below syntax:
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name; Parameter Explanation
The parameters used in the drop trigger syntax are explained as follows:
| Parameter | Descriptions |
|---|---|
| Trigger_name | It is the name of a trigger that we want to remove from the database server. It is a required parameter. |
| Schema_name | It is the database name to which the trigger belongs. If we skip this parameter, the statement will remove the trigger from the current database. |
| IF_EXISTS | It is an optional parameter that conditionally removes triggers only if they exist on the database server. |
If we remove the trigger that does not exist, we will get an error. However, if we have specified the IF EXISTS clause, MySQL gives a NOTE instead of an error.
It is to note that we must have TRIGGER privileges before executing the DROP TRIGGER statement for the table associated with the trigger. Also, removing a table will automatically delete all triggers associated with the table.
MySQL DROP Trigger Example
Let us see how we can drop the trigger associated with the table through an example. So first, we will display all triggers available in the selected database using the below statement:
mysql> SHOW TRIGGERS IN employeedb; After executing the statement, we can see that there are two triggers named before_update_salaries and sales_info_before_update. See the below image:
If we want to remove the before_update_salaries trigger, execute the below statement:
mysql> DROP TRIGGER employeedb.before_update_salaries; It will successfully delete a trigger from the database. If we execute the above statement again, it will return an error message. See the output:
If we execute the above statement again with an IF EXISTS clause, it will return the warning message instead of producing an error. See the output:
mysql> DROP TRIGGER IF EXISTS employeedb.before_update_salaries; 
We can execute the SHOW WARNING statement that generates a NOTE for a non-existent trigger when using IF EXISTS. See the output:
How to Drop trigger in MySQL workbench?
To create an AFTER UPDATE trigger in workbench, we first launch the MySQL Workbench and log in using the username and password. We will get the UI as follows:
Now do the following steps to delete or destroy a trigger associated with the table:
1. Go to the Navigation tab and click on the Schema menu. It will display all databases available in the MySQL database server.
2. Select the database (for example, mystudentdb). Then, double click on the selected schema. It displays the sub-menu containing Tables, Views, Functions, and Stored Procedures. See the below screen.
3. Expand the Tables sub-menu and select a table on which a trigger is associated. Again expand the selected Table -> Triggers; we will get the below image:
4. Now, right-click on the selected table and choose the Alter Table option that gives the screen as below:
5. Now, click on the Trigger tab shown in the previous section’s red rectangular box. You will notice that there is a (+) and (-) icon button to add or delete a trigger:
6. Now, clicking on the (-) button will permanently remove the trigger associated with the table.
Leave a Reply