We must have SUPER privileges to create or destroy triggers with the help of a CREATE or DROP statement. Other than that if the triggered statements use OLD or NEW then there are some additional privileges as follows which we require −To assign the value of a column with SET NEW.column_name = value then we must have the UPDATE privilege for the column.To use NEW.column_name in an expression to refer to the new value of a column, we must have the SELECT privilege for the column.

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        EVENT_OBJECT_SCHEMA: query         EVENT_OBJECT_TABLE: student_info               ACTION_ORDER: 1           ACTION_CONDITION: NULL           ... Read More

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         EVENT_OBJECT_SCHEMA: query          EVENT_OBJECT_TABLE: sample                ACTION_ORDER: 1            ACTION_CONDITION: NULL            ACTION_STATEMENT: BEGIN SET @count ... Read More

It can be done with the help of the INFORMATION_SCHEMA database. Following statement will give us the metadata of triggers −mysql> Select trigger_schema, trigger_name, action_statement     -> from information_schema.triggers\G *************************** 1. row ***************************   trigger_schema: query     trigger_name: trigger_before_delete_sample action_statement: BEGIN SET @count = if (@count IS NULL, 1, (@count+1)); INSERT INTO sample_rowaffected values (@count); END *************************** 2. row ***************************   trigger_schema: query     trigger_name: before_inser_studentage action_statement: IF NEW.age < 0 THEN SET NEW.age = 0; END IF *************************** 3. row ***************************   trigger_schema: sys     trigger_name: sys_config_insert_set_user action_statement: BEGIN IF @sys.ignore_sys_config_triggers != true AND NEW.set_by ... Read More

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 More

We can get the summary output of a column in MySQL result set by using the “WITH ROLLUP” modifier. This modifier is used with GROUP BY CLAUSE. It gives the summary output to include extra rows that represent higher-level summary operations.ExampleIn this example, the WITH ROLLUP modifier gave the summary output with total cost value in the extra row.mysql> Select Item_name, SUM(Cost) AS Total_cost from Item_list GROUP BY Item_name WITH ROLLUP; +-----------+------------+ | Item_name | Total_cost | +-----------+------------+ | Notebook  | 45.00      | | Pen       | 31.70      | | Pencilbox | 125.20     | | NULL      | 201.90     | +-----------+------------+ 4 rows in set (0.00 sec)

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’.

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 tasks because ... Read More

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.

With the help of following MySQL statement, we can use SELECT to calculate the expression −SELECT expression;The above statement is having no reference to any table. Followings are some examples −mysql> Select 10 DIV 5; +----------+ | 10 DIV 5 | +----------+ |        2 | +----------+ 1 row in set (0.06 sec) mysql> Select 10*5; +------+ | 10*5 | +------+ |   50 | +------+ 1 row in set (0.00 sec) mysql> Set @var1 = 100, @var2 = 200; Query OK, 0 rows affected (0.00 sec) mysql> Select @Var1+@Var2; +-------------+ | @Var1+@Var2 | +-------------+ |         300 | +-------------+ 1 row in set (0.00 sec)