Updated June 3, 2023
Definition of MySQL Drop Trigger
MySQL DROP Trigger is a MySQL statement command responsible for deleting an existing trigger from the database. Like other MySQL Triggers, this DROP trigger is also defined as a stored code program invoked when required for a table associated with it. Before we execute the MYSQL DROP trigger, the user should gain the administrative privilege to apply the changes for the database table to generate the desired results. Remember that dropping any table from the MySQL database will automatically delete all the triggers created and linked with the table.
Syntax
The MySQL DROP Trigger has the following basic syntax to delete a stored trigger program associated with that specific database table:
DROP TRIGGER [IF EXISTS] [SchemaName.] TriggerName;From the above structure, we come to know that:
- Initially, you need to provide a particular name to the trigger that must be removed or dropped after the keywords DROP TRIGGER.
- In the next step, please provide the schema name where the trigger was stored or created previously. If the SchemaName is missing or not mentioned, the query statement will delete the trigger in the current MySQL database.
- The syntax includes the addition of IF EXISTS to provide a provisional option to check if the trigger we need to drop is available on that particular schema/table.
How does DROP TRIGGER Statement Work in MySQL?
- MySQL Database tables can implement various triggers. It is best to work only with the tables where the trigger you want to drop is associated.
- The trigger was created earlier, and for some reason or to avoid the event occurring, we can remove the trigger. Using DROP Trigger will also not display the logs stored in the trigger-related table. So, we need to be confirmed and sure if we want to delete the trigger or not.
- According to the above syntax, you need to mention the schema and trigger names with the DROP Trigger statement. At the same time, we must include the IF EXISTS option also.
- Suppose you try to delete a trigger not present in the table without applying the option IF EXISTS; then, the MySQL server will produce an error. Another option is adding EXISTS to the query, which will result in the server generating a NOTE. Instead, you can further inspect the output using the SHOW WARNINGS query command.
- You need to require the administrative level of access to perform the DROP TRIGGER statement for the linked table. Also, dropping the table will result in the removal of all triggers within it.
Examples of MySQL DROP TRIGGER
Let us examine the following examples to demonstrate more about DROP Trigger and its working & uses in MySQL:
In the beginning, we will first create a table in the database for executing the queries. Let it be named as Rooms where the records are stored and for which a trigger will be created.
The SQL query for creating a table is as follows:
DROP TABLE IF EXISTS Rooms;
CREATE TABLE Rooms ( Room_ID INT PRIMARY KEY AUTO_INCREMENT, RoomName VARCHAR(255) NOT NULL, Flat_Num INT NOT NULL);Also, let us enter a few records using the INSERT query:
INSERT INTO Rooms(Room_ID, RoomName, Flat_Num) VALUES('1','pawan Vihar','10');Querying data rows from the Rooms table to see its insides:
SELECT * FROM Rooms;Output:
Again, forming subsequent table to save the variations that happened on the parental table Rooms after any update command if we are using an AFTER UPDATE Trigger as follows:
CREATE TABLE Roomlogs ( User_ID VARCHAR(255), Info_Update VARCHAR(255));View the table at present:
SELECT * FROM Roomlogs;Output:
Now, we are moving towards making the statement to implement an AFTER UPDATE trigger on the Rooms table:
DELIMITER $$
CREATE TRIGGER room_updates
AFTER UPDATE ON Rooms FOR EACH ROW
BEGIN
INSERT into Roomlogs(User_ID,Info_Update) VALUES (user(), CONCAT('Updated Room Info (',OLD.Room_ID,' ',OLD.RoomName,' ',OLD.Flat_Num,') to (',NEW.Room_ID,' ',NEW.RoomName,' ',NEW.Flat_Num,')'));
END$$
DELIMITER ;After generating the room_updates trigger, it will frequently trigger before initiating an update event for every row in the table.
To observe in detail, when you update any value in the Flat_Num column, the system will insert a fresh row into the roomlogs table to record the changes made.
UPDATE Rooms SET Flat_Num = Flat_Num + 5 WHERE Room_ID <10;Output:
Again, view your Roomlogs table and understand the transformation and effect of the AFTER UPDATE trigger using the following query:
SELECT * FROM Roomlogs;Output:
As you can see in the screenshot above, after creating the AFTER UPDATE trigger and making certain modifications in the table rows using the UPDATE query, the Roomlogs table has recorded the user id and information about the changes made in the trigger-related table Rooms. Suppose we will create another trigger in the Payment table as follows:
Payment table
Payment_logs table to record the logs after an update query:
Trigger Code:
DELIMITER $$
CREATE TRIGGER 'payment_update'
AFTER UPDATE ON 'payment' FOR EACH ROW
BEGIN
IF OLD.PAmount <> new.PAmount THEN
INSERT INTO Payment_logs (User_ID,Before_PAmount, After_PAmount)
VALUES (user(), old.PAmount, new.PAmount);
END IF;
END$$
DELIMITER ;You can even view the trigger present in the Rooms table by going to the Triggers option in the phpMyAdmin server as follows:
SHOW TRIGGERS;Output:
Finally, we will use DROP Trigger in MySQL to show the result on table Rooms. Let us delete the second trigger, named room_updates, using the SQL command below:
DROP TRIGGER room_updates;Again, view the triggers on table Rooms to validate the removal of the trigger mentioned above.
SHOW TRIGGERS;Output:
The trigger has deleted its log info from the Rooms table.
Conclusion
- Normally, we create MySQL triggers to inform the user of any database changes like MySQL operations: INSERT, DELETE, UPDATE, and ALTER statement queries, and keep a log of those modifications.
- When we need to remove any trigger available to a specific table, we will use the MySQL DROP Trigger command statement in that database table.
Recommended Articles
We hope that this EDUCBA information on “MySQL DROP TRIGGER” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
