In MySQL, how can I insert or update interrupt records in a trigger?

Unlike some other databases, MySQL can throw an exception in the trigger to interrupt the execution of the trigger to prevent the execution of the corresponding SQL statement. The MySQL directory version cannot directly throw an exception. How can we achieve this?

 

The following is an implementation method. The idea is to try to use an incorrect statement in the trigger to interrupt code execution.

 

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>