Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
Database Articles
Page 259 of 547
How can we destroy a trigger?
We can destroy a trigger in two ways −Dropping a trigger explicitlyWith the help of the DROP statement, we can destroy a trigger explicitly. It can be understood with the help of the following example −mysql> DROP Trigger before_inser_studentage1; Query OK, 0 rows affected (0.05 sec)Dropping a trigger implicitlyA trigger will be destroyed implicitly if the table with which it is associated is destroyed or if the database which it is associated is destroyed.
Read MoreHow can we use INFORMATION_SCHEMA to get the details about triggers in a particular database?
It can be done with the help of the following statement −mysql> select * from information_schema.triggers where -> information_schema.triggers.trigger_schema like '%query%'\G *************************** 1. row *************************** TRIGGER_CATALOG: def TRIGGER_SCHEMA: query TRIGGER_NAME: trigger_before_delete_sample EVENT_MANIPULATION: DELETE EVENT_OBJECT_CATALOG: def ...
Read MoreHow to use MySQL DISTINCT clause on multiple columns?
We can use the DISTINCT clause on more than columns in MySQL. In this case, the uniqueness of rows in the result set would depend on the combination of all columns.ExampleConsider the following table ‘testing’ having 10 rows −mysql> select * from testing; +------+---------+---------+ | id | fname | Lname | +------+---------+---------+ | 200 | Raman | Kumar | | 201 | Sahil | Bhalla | | 202 | Gaurav | NULL | | 203 | Aarav | NULL | | 204 | Harshit | Khurana | | 205 | Rahul ...
Read MoreHow Can MySQL GROUP BY clause behave like DISTINCT clause?
When we use the GROUP BY clause in the SELECT statement without using aggregate functions then it would behave like the DISTINCT clause. For example, we have the following table −mysql> Select * from testing; +------+---------+---------+ | id | fname | Lname | +------+---------+---------+ | 200 | Raman | Kumar | | 201 | Sahil | Bhalla | | 202 | Gaurav | NULL | | 203 | Aarav | NULL | | 204 | Harshit | Khurana | | 205 | Rahul | NULL | | 206 | ...
Read MoreHow we can find all the triggers associated with a particular MySQL table?
We can find all the triggers associated with a particular table with the help of the following query −mysql> Select * from INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA = 'query'AND EVENT_OBJECT_TABLE = 'Student_info'\G *************************** 1. row *************************** TRIGGER_CATALOG: def TRIGGER_SCHEMA: query TRIGGER_NAME: studentinfo_after_delete EVENT_MANIPULATION: DELETE EVENT_OBJECT_CATALOG: def ...
Read MoreHow does 'FOR EACH ROW' work in the MySQL trigger?
Actually ‘FOR EACH ROW’ means for each of the matched rows that get either updated or deleted. In other words, we can say that trigger is not applied to each row, it just says to execute the trigger body for each affected table row. We can illustrate this by the following example −ExampleIn this example, we are creating two tables, Sample and Sample_rowaffected, as follows −mysql> Create table Sample(id int, value varchar(20)); Query OK, 0 rows affected (0.47 sec) mysql> Insert into Sample(id, value) values(100, 'same'), (101, 'Different'), (500, 'excellent'), (501, 'temporary'); Query OK, 4 rows affected (0.04 sec) ...
Read MoreWhat is the meaning of 'empty set' in MySQL result set?
If there is ‘empty set’ in the result set of MySQL query then it means that MySQL is returning no rows and no error also in the query. It can be understood with the help of the following example −mysql> Select * from Student_info WHERE Name = 'ABCD'; Empty set (0.00 sec)We can see the empty set and execution time as output. It means that the query is correct but the MySQL table is not having the name ‘ABCD’.
Read MoreHow can we check the list of all triggers in a database?
With the help of the SHOW TRIGGERS statement, we can list all the triggers in a particular database. It can be illustrated with the help of the following example −Examplemysql> 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, ERR OR_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. ...
Read MoreHow can we discard a MySQL statement in the middle of its processing?
With the help of \c command, we can discard a MySQL statement in the middle of its processing. Consider the following example in which in the middle of the statement we want to discard it then we use \c option for it −mysql> Select * -> from\c mysql>The above query shows that after using \c option MySQL discards the statement and returns to the prompt.
Read MoreWhat are the advantages, disadvantages and restrictions of using MySQL triggers?
We must have to understand the advantages, disadvantages, and restrictions of using MySQL triggers so that we can use it effectively.AdvantagesFollowings are the advantages of using MySQL triggers −Integrity of data − With the help of MySQL trigger we can check the integrity of data in the table. In other words, MySQL triggers are the alternative way to check the integrity of data.Useful for catching errors − MySQL triggers can catch errors in business logic in the database layer.Alternative way to run scheduled tasks − Actually by using MySQL triggers we do not have to wait to run the scheduled ...
Read More