MySQL notes trigger application

Create a trigger

Create a trigger with only one execution statement
Copy codeThe Code is as follows:
Create trigger name BEFORE | after trigger event
ON Table name for each row execution statement

The trigger name parameter indicates the name of the trigger to be created.

The BEFORE and AFTER Parameters specify the execution time BEFORE or AFTER the event.

For each row indicates that the trigger event is triggered when any operation on the record meets the requirements.
Copy codeThe Code is as follows:
Mysql> create trigger trig1 AFTER INSERT
-> ON work FOR EACH ROW
-> Insert into time VALUES (NOW ());
Query OK, 0 rows affected (0.09 sec)

A trigger named trig1 is created above. Once there is an insert action in work, it will automatically Insert the current time to the time table.

Create a trigger with multiple execution statements
Copy codeThe Code is as follows:
Create trigger name BEFORE | after trigger event
ON Table name FOR EACH ROW
BEGIN
Execution Statement List
END

The execution statement list parameter between BEGIN and END indicates the multiple statements to be executed. Different statements are separated by semicolons (;).

Tips: In general, mysql uses; as the end execution statement by default, which conflicts with the branch required in the trigger.

DELIMITER can be used to solve this problem, for example, DELIMITER |. The end symbol can be changed to |

After the trigger is created, you can use DELIMITER; to change the end symbol;
Copy codeThe Code is as follows:
Mysql> DELIMITER |
Mysql> create trigger trig2 BEFORE DELETE
-> ON work FOR EACH ROW
-> BEGIN
-> Insert into time VALUES (NOW ());
-> Insert into time VALUES (NOW ());
-> END
-> |
Query OK, 0 rows affected (0.06 sec)

Mysql> DELIMITER;

In the preceding statement, the ending symbol is defined as |, and a trigger is defined in the middle. Once a deletion operation meets the conditions

The BEGIN and END statements will be executed, and then the | END

Use DELIMITER; to restore the end symbol

View triggers
Show triggers statement to view trigger information
Copy codeThe Code is 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 basic information of all triggers is displayed.

Tips: The show triggers statement cannot query the specified trigger.

View trigger information in the triggers table
Copy codeThe Code is 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. At the same time, this method can query the details of the trigger.
Copy codeThe Code is as follows:
Mysql> SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME = 'trigger' \ 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 in the information_schema database.

You can use the SELECT statement to query. If there are too many trigger information, you 'd better specify the query using the TRIGGER_NAME field.

Delete trigger
Copy codeThe Code is as follows:
Mysql> drop trigger trig1;
Query OK, 0 rows affected (0.04 sec)

After deleting a trigger, you are advised to use the above method to view it again.

You can also use database. trig to specify the trigger in a database.

Tips:If you do not need a trigger, delete it to avoid unexpected operations.