- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What happens with the trigger when we will drop the table having that trigger?
The trigger would also be destroyed if we will destroy the table with which it is associated. It is called the explicit destroy of a trigger. It can be understood with the help of an example −
Example
Suppose we have a trigger named ‘trigger_before_delete_sample’ on the table named ‘Sample’. Now if we will delete this table then the above trigger associated with this table would also be deleted.
mysql> Show Triggers\G *************************** 1. row *************************** Trigger: trigger_before_delete_sample Event: DELETE Table: sample Statement: BEGIN SET @count = if (@count IS NULL, 1, (@count+1)); INSERT INTO sample_rowaffected values (@count); END Timing: BEFORE Created: 2017-11-21 12:31:58.70 sql_mode: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: cp850 collation_connection: cp850_general_ci Database Collation: latin1_swedish_ci *************************** 2. row *************************** Trigger: before_inser_studentage Event: INSERT Table: student_age Statement: IF NEW.age < 0 THEN SET NEW.age = 0; END IF Timing: BEFORE Created: 2017-11-21 11:26:15.34 sql_mode: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: cp850 collation_connection: cp850_general_ci Database Collation: latin1_swedish_ci 2 rows in set (0.01 sec)
The above result set shows that there are two triggers in the database among these there is a trigger that is associated with a table named ‘Sample’. Now with the help of the following statement, we will DROP the table −
mysql> DROP table sample; Query OK, 0 rows affected (0.26 sec)
Now with the help of the following statement, we can see that the trigger which was associated with a table named ‘Sample’ is also destroyed.
mysql> Show Triggers\G *************************** 1. row *************************** Trigger: before_inser_studentage Event: INSERT Table: student_age Statement: IF NEW.age < 0 THEN SET NEW.age = 0; END IF Timing: BEFORE Created: 2017-11-21 11:26:15.34 sql_mode: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: cp850 collation_connection: cp850_general_ci Database Collation: latin1_swedish_ci 1 row in set (0.01 sec)
- Related Articles
- How will you create a new TRIGGER on the ORDERS DB2 table? Give the syntax of TRIGGER
- Drop trigger if exists in MySQL?
- How can we destroy a trigger?
- MySQL trigger to insert row into another table?
- How can I clone/duplicate the table along with its data, trigger and indexes?
- Implement MySQL trigger in the first table to insert records in the second table?
- How can we create and use a MySQL trigger?
- Why do we need to change the delimiter for creating a trigger?
- How to trigger the same function with jQuery multiple events?
- What MySQL returns when we remove all the columns from a table by using ALTER TABLE command with DROP keyword?
- Difference Between Trigger and Procedure
- How do I trigger a function when the time reaches a specific time in JavaScript?
- How can we create multiple MySQL triggers for the same trigger event and action time?
- How can MySQL handle the errors during trigger execution?
- What does DELIMITER // do in a Trigger in MySQL?
