Use of MySQL triggers

Overview:

When a specific database event occurs for a specific table, the trigger executes the corresponding SQL command.

Syntax:

The general commands for creating triggers are as follows:

CREATE [temp|temporary] Trigger name

[Before|after] [Insert|delete|update|update of columns] On table

Action

use of triggers:Create:

Mysql> CREATE TRIGGER Stu_trigger after INSERT
-On students
For each ROW
INSERT into info (stu_id, info) VALUES (new.id, ");
Query OK, 0 rows affected (0.07 sec)

Trigger Event:

Mysql> INSERT into students (ID, name, age) VALUES (4, ' Zeus ', 56400);
Query OK, 1 row Affected (0.00 sec)

Verify Students Table results:

Mysql> SELECT * from students;
+------+----------------+--------+
| ID | name | Age |
+------+----------------+--------+
| 1 |    Bumblebee | 800 |
| 2 |  King of Monkey | 10000 |
| 3 | Medusa | 100000 |
| 4 |  Zeus | 56400 |
+------+----------------+--------+
4 rows in Set (0.00 sec)

Verify the Info table results:

Mysql> SELECT * from info;
+----+--------+---------------------------------+
| ID | stu_id | info |
+----+--------+---------------------------------+
|      1 | 1 | A member of the deformed steel. |
|      2 | 2 |      Hero in Chinese mythology. |
|      3 | 3 |  In Greek mythology the Gorgon. |
|      5 |                                 4 | |
+----+--------+---------------------------------+
4 rows in Set (0.00 sec)

Note: The new ID for this is 5 and not 4 because it was previously caused by a delete operation on the info table.

to view information about a trigger:

Mysql> SELECT * from Information_schema.triggers;
+----------------+--------------+--------------------+---------------------+
| Trigger_schema | trigger_name | event_manipulation | Event_object_schema |
+----------------+--------------+--------------------+---------------------+
| Student | Stu_trigger | INSERT | Student |
+----------------+--------------+--------------------+---------------------+


+--------------------+----------------------------------------------------+
| event_object_table | action_statement |
+--------------------+----------------------------------------------------+
| Students | INSERT into info (stu_id, info) VALUES (new.id, ') |
+--------------------+----------------------------------------------------+

1 row in Set (0.19 sec)

Note: The above printing information is a truncated version of the complete information, you can view in the environment itself.

To delete a trigger:

mysql> DROP TRIGGER Stu_trigger;
Query OK, 0 rows affected (0.01 sec)

To verify the deletion result:mysql> SELECT * from Information_schema.triggers;
Empty Set (0.01 sec)

Note that the deletion succeeded

Use of MySQL triggers