MySQL DROP TRIGGER

Summary: in this tutorial, you will learn how to use the MySQL DROP TRIGGER statement to drop a trigger from the database.

Introduction to MySQL DROP TRIGGER statement

The DROP TRIGGER statement deletes a trigger from the database.

Here is the basic syntax of the DROP TRIGGER statement:

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

In this syntax:

• First, specify the name of the trigger that you want to drop after the DROP TRIGGER keywords.
• Second, specify the name of the schema to which the trigger belongs. If you skip the schema name, the statement will drop the trigger in the current database.
• Third, use IF EXISTS option to conditionally drop the trigger if the trigger exists. The IF EXISTS clause is optional.

If you drop a trigger that does not exist without using the IF EXISTS clause, MySQL issues an error. However, if you use the IF EXISTS clause, MySQL issues a NOTE instead.

The DROP TRIGGER requires the TRIGGER privilege for the table associated with the trigger.

Note that if you drop a table, MySQL will automatically drop all triggers associated with the table.

MySQL DROP TRIGGER example

First, create a table called billings for demonstration:

CREATE TABLE billings (
    billingNo INT AUTO_INCREMENT,
    customerNo INT,
    billingDate DATE,
    amount DEC(10 , 2 ),
    PRIMARY KEY (billingNo)
);
Code language: SQL (Structured Query Language) (sql)

Second, create a new trigger called BEFORE UPDATE that is associated with the billings table:

DELIMITER $$
CREATE TRIGGER before_billing_update
    BEFORE UPDATE 
    ON billings FOR EACH ROW
BEGIN
    IF new.amount > old.amount * 10 THEN
        SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'New amount cannot be 10 times greater than the current amount.';
    END IF;
END$$    
DELIMITER ;
Code language: SQL (Structured Query Language) (sql)

If you are not familiar with the DELIMITER statement, check it out here in the stored procedure statement.

The trigger activates before any update. If the new amount is 10 times greater than the current amount, the trigger raises an error.

Third, show the triggers:

SHOW TRIGGERS;
Code language: SQL (Structured Query Language) (sql)

Fourth, drop the before_billing_update trigger:

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

Finally, show the triggers again to verify the removal:

SHOW TRIGGERS;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the MySQL DROP TRIGGER statement to drop a trigger from the database.