Does MySQL interrupt the insertion or update of a record in a trigger? _mysql
Source: Internet
Author: User
Here is a way to implement it. The idea is to find a way to interrupt the execution of the code by using an error statement in the trigger.
Mysql> CREATE TABLE T_control (ID int primary key);
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO T_control values (1);
Query OK, 1 row affected (0.05 sec)
Mysql> CREATE TABLE T_bluerosehero (ID int primary key,col int);
Query OK, 0 rows affected (0.11 sec)
Mysql> delimiter//
Mysql> CREATE trigger Tr_t_bluerosehero_bi before insert on T_bluerosehero
-> for each row
-> begin
-> if New.col>30 Then
-> INSERT INTO T_control values (1);
-> End If;
-> end;
->//
Query OK, 0 rows affected (0.08 sec)
Mysql> delimiter;
Mysql>
mysql> INSERT into T_bluerosehero values (1,20);
Query OK, 1 row affected (0.25 sec)
mysql> INSERT into T_bluerosehero values (2,40);
ERROR 1062 (23000): Duplicate entry ' 1 ' for key ' PRIMARY '
Mysql>
Mysql> select * from T_bluerosehero;
+----+------+
| ID | Col |
+----+------+
| 1 | 20 |
+----+------+
1 row in Set (0.00 sec)
Mysql>
Or
Mysql> delimiter//
Mysql> CREATE trigger Tr_t_bluerosehero_bi before insert on T_bluerosehero
-> for each row
-> begin
-> declare i int;
-> if New.col>30 Then
-> INSERT into XXXX values (1);
-> End If;
-> end;
->//
Query OK, 0 rows affected (0.06 sec)
Mysql> delimiter;
Mysql> Delete from T_bluerosehero;
Query OK, 3 rows affected (0.05 sec)
mysql> INSERT into T_bluerosehero values (1,20);
Query OK, 1 row affected (0.06 sec)
mysql> INSERT into T_bluerosehero values (2,40);
ERROR 1146 (42S02): Table ' csdn.xxxx ' doesn ' t exist
Mysql>
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