EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL DROP TRIGGER

MySQL DROP TRIGGER

MySQL DROP TRIGGER

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:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

MySQL DROP TRIGGER-1.1

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:

MySQL DROP TRIGGER-1.2

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:

MySQL DROP TRIGGER-1.3

Again, view your Roomlogs table and understand the transformation and effect of the AFTER UPDATE trigger using the following query:

SELECT * FROM Roomlogs;

Output:

MySQL DROP TRIGGER-1.4

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

MySQL DROP TRIGGER-1.5

Payment_logs table to record the logs after an update query:

Output-1.6

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:

Output-1.7

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:

Output-1.8

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.

  1. MySQL IF Function
  2. UUID in MYSQL
  3. MySQL sum()
  4. MySQL Partition
C++ PROGRAMMING Certification Course
38+ Hours of HD Videos
9 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ASP.NET Certification Course
149+ Hours of HD Videos
28 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SQL - Everything in SQL
253+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SOFTWARE TESTING Certification Course
74+ Hour of HD Videos
13 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
MYSQL Certification Course
 115+ Hours of HD Videos
18 Courses
3 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more