Learn notes MySQL triggers detailed

Creating triggers

Create a trigger with only one execution statement
CREATE TRIGGER Trigger Name before| After Trigger event
On table name for each ROW executes the statement where the trigger name parameter refers to the name of the trigger to be created

1. Create MySQL triggers:

Grammar:

The code is as follows	Copy Code
CREATE TRIGGER trigger_name trigger_time trigger_event on Tbl_name For each ROW BEGIN Trigger_stmt End; CREATE TRIGGER trigger_name trigger_time trigger_event on Tbl_name For each ROW BEGIN Trigger_stmt End;

Example

The code is as follows	Copy Code
CREATE TRIGGER setuserhome after insert on users For each ROW BEGIN Update ' users ' set Homelocationx = 128, homelocationy=128, homelocationz=30 where uuid = New.uuid End

The above example is wrong, let the table to be triggered when the update will let the program into the dead loop.

The system will report this error: It is already used by statement which invoked this stored Function/trigger.

Should be changed to the following statement:

The code is as follows	Copy Code
CREATE TRIGGER setuserhome before insert on users For each ROW BEGIN Set New.homelocationx = 128; Set new.homelocationy = 128; Set new.homelocationz=30; End

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

The code is as follows	Copy Code
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) creates a trigger named TRIG1 that automatically inserts the current time into the table once there is an insert action in the work

To create a trigger with multiple execution statements

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

The code is as follows	Copy Code
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

The code is as follows	Copy Code
Mysql> show TRIGGERSG; 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 results show basic information for all triggers

Tips:show Triggers statement cannot query the specified trigger

Viewing trigger information in the Triggers table

The code is as follows	Copy Code
Mysql> SELECT * from INFORMATION_SCHEMA.TRIGGERSG 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

The code is as follows	Copy Code
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:worktips

: 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

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 don't need a trigger, be sure to delete the trigger, so you don't have to do it accidentally.