Application _mysql of Mysql note triggers

Creating triggers

Create a trigger with only one execution statement

Copy Code code as follows:

CREATE TRIGGER Trigger Name before| After Trigger event
On table name for each ROW EXECUTE statement

Where the trigger name parameter refers to the name of the trigger to be created

The Before and after parameters specify when the execution is triggered, before or after the event

For each row indicates that an action on any record that satisfies a trigger event triggers the trigger

Copy Code code as follows:

Mysql> CREATE TRIGGER trig1 after INSERT
-> on work for each ROW
-> inserts into the time VALUES (now ());
Query OK, 0 rows affected (0.09 sec)

A trigger named TRIG1 is created, and once the insert action is inserted in the work, the current time is automatically inserted into the timeline

To create a trigger with multiple execution statements

Copy Code code as follows:

CREATE TRIGGER Trigger Name before| After Trigger event
On table name for each ROW
BEGIN
Execute statement List
End

Where the execution statement list parameter between Begin and end represents multiple statements that need to be executed and separate statements separated by semicolons

Tips: Under normal circumstances, MySQL default is to; As an end execution statement that conflicts with the branch required in the trigger

To resolve this issue, you can use DELIMITER, such as: DELIMITER | |, to turn the end symbol into a | |

When the trigger is created, you can use delimiter to turn the end symbol into;

Copy Code code as follows:

Mysql> DELIMITER | |
Mysql> CREATE TRIGGER Trig2 before DELETE
-> on work for each ROW
-> BEGIN
-> inserts into the time VALUES (now ());
-> inserts into the time VALUES (now ());
-> End
-> | |
Query OK, 0 rows affected (0.06 sec)

Mysql> DELIMITER;

In the above statement, the ending symbol is defined as a | |, the middle defines a trigger, and once a delete operation satisfies the condition

Executes the statements in the begin and end, and then uses the | | End

The final use of delimiter; Restores the end symbol

viewing triggers
Show triggers statement view trigger information

Copy Code code as follows:

Mysql> show Triggers\g;
1. Row ***************************
Trigger:trig1
Event:insert
Table:work
Statement:insert into Time VALUES (now ())
Timing:after
Created:null
Sql_mode:
Definer:root@localhost
Character_set_client:utf8
Collation_connection:utf8_general_ci
Database Collation:latin1_swedish_ci

The results will show the basic information for all triggers

Tips:show Triggers statement cannot query the specified trigger

Viewing trigger information in the Triggers table

Copy Code code as follows:

Mysql> SELECT * from Information_schema.triggers\g
1. Row ***************************
Trigger_catalog:def
Trigger_schema:person
Trigger_name:trig1
Event_manipulation:insert
Event_object_catalog:def
Event_object_schema:person
Event_object_table:work
action_order:0
Action_condition:null
Action_statement:insert into Time VALUES (now ())

The results show the details of all triggers, and the method can query the details of a trigger

Copy Code code as follows:

Mysql> SELECT * from information_schema.triggers WHERE trigger_name= ' trig1 ' \g
1. Row ***************************
Trigger_catalog:def
Trigger_schema:person
Trigger_name:trig1
Event_manipulation:insert
Event_object_catalog:def
Event_object_schema:person
Event_object_table:work

Tips:All trigger information is stored in the Triggers table under the INFORMATION_SCHEMA database

You can query using the SELECT statement, and if there are too many triggers, it is best to specify the query through the Trigger_name field

deleting triggers

Copy Code code as follows:

mysql> DROP TRIGGER trig1;
Query OK, 0 rows affected (0.04 sec)

After you delete a trigger, it's best to use the above method to view it again

You can also use Database.trig to specify triggers in a database

tips: If you do not need a trigger, be sure to delete the trigger to avoid accidental action