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 TRIGGERkeywords. - 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 EXISTSoption to conditionally drops the trigger if the trigger exists. TheIF EXISTSclause 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.