MySQL DROP TRIGGER Statement – A Complete Guide

In this tutorial, we will learn about the DROP TRIGGER statement with the syntax and example. Before diving into the topic, we will have a look at an introduction and the prerequisites.

To check how the DROP TRIGGER statement works, we will create a trigger first and then drop that trigger. We will see how you can remove a trigger from the currently selected schema as well as from another schema. Let’s go!

Introduction and the Prerequisites

If you are new to the MySQL trigger topic, you can check out our guide on introduction to MySQL triggers. It is important to know the terminologies of the database triggers.

The DROP TRIGGER statement is used to remove the trigger from your database. Using the DROP TRIGGER statement, you can delete the trigger permanently from your system. But before that, you must have a trigger privilege for the database or the table associated with the trigger. If you want to know more about MySQL privileges, you should read our guide on MySQL users and privileges.

Here, we are using a MySQL command-line client to manage the database. You can use the MySQL workbench as well to run your queries.

Syntax of DROP TRIGGER Statement

You can remove a trigger from your database using the DROP TRIGGER statement. Following is the complete syntax of the DROP TRIGGER statement.

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;Code language: SQL (Structured Query Language) (sql)

The statements inside the square brackets ([…]) are optional.

Where,

• DROP TRIGGER – It is a statement to drop the trigger.
• IF EXISTS –  It prevents generating an error if you try to delete a trigger that does not exist for the schema.
• schema_name – It is a database name. If you don’t specify the schema name then the trigger from the currently selected database will be dropped.
• trigger_name – The trigger name that you want to delete.

Examples of DROP TRIGGER Statement

Here, we will create a trigger on an already created table, and then we will try to delete it.

Creating a trigger

For the demonstration purpose, we are creating a BEFORE INSERT trigger.

DELIMITER //
CREATE TRIGGER before_insert
BEFORE INSERT ON s1
FOR EACH ROW
SET NEW.name = CONCAT("Mr. ",NEW.name);
//Code language: SQL (Structured Query Language) (sql)

We have created a trigger of the name “before_insert” on the table “s1”.

Here, we are trying to insert the new name in the table with the “Mr” attached before the name. The delimiter here helps MySQL to tell that there is a block of multiple statements that will be executed in one go just like a stored procedure or a stored function.

Displaying a Trigger

Now, we skip the testing part and display the trigger from the table “s1”. You can read our detailed guide on the MySQL SHOW TRIGGERS statement (link to the SHOW TRIGGERS ARTICLE).

SHOW TRIGGERS FROM journaldev like 's1' \G;Code language: SQL (Structured Query Language) (sql)

Here, we have displayed the trigger from the “s1” table of the journaldev database. The \G modifier is used here to display the result vertically.

Deleting a Trigger Using DROP TRIGGER Statement

Now, we will delete a trigger ‘before_insert’ from our database. Note that, we will first select the default database using the “USE db_name” statement and then remove the trigger.

USE journaldev;Code language: SQL (Structured Query Language) (sql)

Here, the journaldev is set to the default database so we don’t have to specify the DB name every time we write a query.

Now, let’s use the DROP TRIGGER statement to delete the trigger.

DROP TRIGGER before_insert;Code language: SQL (Structured Query Language) (sql)

As you can see, the trigger is deleted successfully.

Now, we will again create the same trigger but this time, we will delete that trigger without defining a default database. So, it’s like deleting a trigger from another database.

Deleting a Trigger From Another Database

We have created the trigger again. We will not show the process here again. We will only see if the trigger exists when we re-create it.

SHOW TRIGGERS FROM journaldev like "s1" \G;Code language: SQL (Structured Query Language) (sql)

Yes, the trigger exists. Let’s delete it.

DROP trigger journaldev.before_insert;Code language: SQL (Structured Query Language) (sql)

As you can see, the trigger is deleted successfully.

Deleting a Trigger That Doesn’t Exist

Let’s check if you try to delete a trigger that doesn’t exist in the database.

DROP trigger journaldev.before_insert;Code language: SQL (Structured Query Language) (sql)

As you can see here, you get an error that says the trigger does not exist. Here, you can use the ‘IF EXISTS’ clause to prevent the error.

DROP trigger IF EXISTS journaldev.before_insert;Code language: SQL (Structured Query Language) (sql)

Here, the query was executed successfully but we have a warning which is the default functionality of the statement.

Conclusion

So in this tutorial, we learned about the DROP TRIGGER statement. I hope you have understood this topic because it is straightforward and easy to understand as compared to other trigger topics. See you in another exciting tutorial!

References

MySQL official documentation on drop trigger.