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;
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 drops the trigger if the trigger exists. TheIF 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)
);
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 ;
If you are not familiar with the DELIMITER
the 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;
Fourth, drop the before_billing_update
trigger:
DROP TRIGGER before_billing_update;
Finally, show the triggers again to verify the removal:
SHOW TRIGGERS;
In this tutorial, you have learned how to use the MySQL DROP TRIGGER
statement to drop a trigger from the database.
0 Comments
CAN FEEDBACK
Emoji