MySQL Study Notes 15: triggers

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 TriggerTrigger name before|After trigger eventOn Table Name ForEach 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 TriggerTrig1 afterInsert-> On Work ForEach row-> Insert IntoTimeValues(Now (); query OK,0Rows affected (0.09Sec)
 

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 TriggerTrigger name before|After trigger eventOnTable NameForEach rowBeginExecution Statement ListEnd
 

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  @ Localhost  Character_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 TriggerTrig1; query OK,0Rows affected (0.04Sec)
 

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.