MariaDB Drop Trigger

In a database management system(DBMS), triggers are essential for automating actions based on specific events. However, there are times when certain triggers need to be removed or dropped from the database.

In MariaDB, the DROP TRIGGER statement provides a straightforward way to achieve this task. In this article, we’ll explore what is DROP triggers and how to use DROP triggers by understanding various examples and so on.

Drop Trigger

The DROP TRIGGER statement in MariaDB removes a trigger from a table in the database. Triggers are database objects that automatically perform actions in response to specified events, such as INSERT, UPDATE, or DELETE operations on a table.

Syntax:

DROP TRIGGER [IF EXISTS] [db_name.]trigger_name;

Explanation: This DROP TRIGGER is used to delete a trigger from a table. The optional IF EXISTS clause prevents an error if the trigger does not exist. The db_name parameter specifies the database where the trigger is located.

How to Delete a Trigger in MariaDB?

The DROP TRIGGER statement is used to delete unwanted triggers in the table of the database. We may also add the IF EXISTS conditional operator to check if the trigger already exists to prevent potential error if the trigger does not exist.

The database name can also be provided along with the table name. The statement can run only if the user has permission and there are no effects.

Let’s set up an Environment to Perform Drop Triggers Operation

To understand How to Delete a Trigger in MariaDB we need a table on which we will perform various operations and queries. Here we will consider a table called customers which contains id, name, email, phone_number, and active as Columns.

CREATE TABLE customers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  phone_number VARCHAR(20),
  active BOOLEAN DEFAULT TRUE
);

Output:

Creation of customers table

Explanation: The query creates a table named as customers with id, name, email, phone_number and active columns with their respective constraints.

Example of Drop Trigger

Let’s create an Trigger before performing DROP operations.

The following queries are used to create the triggers for showcasing the example.

Creating Trigger for INSERT Operation

The query creates a trigger named as insert_the_row for the INSERT operation in the customers table.

CREATE TRIGGER insert_the_row
BEFORE INSERT ON customers
FOR EACH ROW
INSERT INTO customers(NAME,email,phone_number,active) 
VALUES (NEW.name,NEW.email,NEW.phone_number,1);

-- Use the below query to display the created triggers.
SHOW TRIGGERS;

Output:

Creating trigger for INSERT operation

Explanation: The query creates a trigger named as insert_the_row before the INSERT event in the customers table. The statement carried out by the triggers is a INSERT operation to the customers table with the same values that is inserted but with active column hard coded as 1.

Creating Trigger for DELETE Operation

The query creates a trigger named as delete_the_row for the DELETE operation in the customers table.

CREATE TRIGGER delete_the_row
BEFORE DELETE ON customers
FOR EACH ROW
INSERT INTO customers(NAME,email,phone_number,active) 
VALUES (OLD.name,OLD.email,OLD.phone_number,0);

-- Use the below query to display the created triggers. 
SHOW TRIGGERS;

Output:

Creating trigger for DELETE operation

Explanation: The query creates a trigger named as delete_the_row before the DELETE event in the customers table. The statement carried out by the triggers is a INSERT operation to the customers table with the old values of the deleted row but with active column hard coded as 0.

Creating Trigger for UPDATE Operation

The query creates a trigger named as update_the_row for the UPDATE operation in the customers table.

CREATE TRIGGER update_the_row
BEFORE UPDATE ON customers
FOR EACH ROW
INSERT INTO customers(NAME,email,phone_number,active) 
VALUES (OLD.name,OLD.email,OLD.phone_number,0);

Output:

Creating trigger for UPDATE operation

Explanation: The query creates a trigger named as update_the_row before the UPDATE event in the customers table. The statement carried out by the triggers is a INSERT operation to the customers table with the old values of the updated row but with active column hard coded as 0.

Example of Dropping Triggers

The following queries showcase how to drop a trigger with and without the IF EXISTS conditional operator.

Dropping a Trigger Without IF EXISTS

The following query drops the trigger named as insert_the_row.

DROP TRIGGER insert_the_row;

Output:

Dropping the insert_the_row trigger

Explanation: The query deletes the insert_the_row trigger which was created for the INSERT operation in the customers table.

Dropping a Trigger With IF EXISTS

The following query drops all triggers in the customers table.

DROP TRIGGER IF EXISTS update_the_row;

Output:

Dropping the update_the_row trigger

Explanation: The query deletes the update_the_row trigger which was created for the UPDATE operation in the customers table.

Conclusion

Triggers are a great tool for automating data management but at times to we may need to drop them. The deletion can be done very easily using the DROP TRIGGER statement but it can process only if the user have adequate permission and there is no cascading effects. This deletion ensure removal of unwanted triggers and ensures data integrity.