MySQL study notes 15: trigger _ MySQL

MySQL study note 15: trigger bitsCN.com

A trigger is an event that triggers an operation. These events include INSERT statements, UPDATE statements, and DELETE statements.

Create a trigger to create a trigger with only one execution statement

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.

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

Create trigger name BEFORE | after trigger event ON table name for each rowbegin 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;

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 the trigger information in the show triggers statement.

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@localhostcharacter_set_client: utf8collation_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

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.

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

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.

BitsCN.com