Learn notes MySQL triggers detailed

Source: Internet
Author: User
Tags current time uuid

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.