Javatpoint Logo
Javatpoint Logo

MySQL DROP Trigger

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:

  1. MySQL Command Line Client
  2. 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:

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:

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:

MySQL DROP Trigger

If we want to remove the before_update_salaries trigger, execute the below statement:

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:

MySQL DROP Trigger

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

We can execute the SHOW WARNING statement that generates a NOTE for a non-existent trigger when using IF EXISTS. See the output:

MySQL DROP Trigger

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:

MySQL DROP Trigger

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.

MySQL DROP Trigger

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.

MySQL DROP Trigger

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:

MySQL DROP Trigger

4. Now, right-click on the selected table and choose the Alter Table option that gives the screen as below:

MySQL DROP Trigger

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:

MySQL DROP Trigger

6. Now, clicking on the (-) button will permanently remove the trigger associated with the table.






Youtube For Videos Join Our Youtube Channel: Join Now

Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA