Use a trigger to stop an insert or update in MySQL?

MySQLMySQLi Database

You need to use SIGNAL SQL STATE command to stop an insert or update in MySQL. The trigger syntax is as follows:

DELIMITER //
CREATE TRIGGER yourTriggerName BEFORE INSERT ON yourTableName FOR EACH ROW
BEGIN
yourCondition THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'anyMessageToEndUser';
END //
DELIMITER ;

Now, create a trigger that would prevent to insert a record in the table on some condition. The query to create a trigger is as follows:

mysql> DELIMITER //
mysql> CREATE TRIGGER InsertPreventTrigger BEFORE INSERT ON Insert_Prevent
   -> FOR EACH ROW
   -> BEGIN
   -> IF(new.Id < 1 or new.Id > 5) THEN
   -> SIGNAL SQLSTATE '45000'
   -> SET MESSAGE_TEXT = 'You can not insert record';
   -> END IF;
   -> END //
Query OK, 0 rows affected (0.20 sec)
mysql> DELIMITER ;

The above trigger will stop to insert whenever you insert a record less than 0 or greater than 5.

Let us now create a table first. The query to create a table is as follows:

mysql> create table Insert_Prevent
   -> (
   -> Id int
   -> );
Query OK, 0 rows affected (0.62 sec)

Now insert the record less than 0 or greater than 5. This will result in an error message since the trigger is created to stop inserting whenever you insert a record less than 0 or greater than 5. The error message is as follows:

mysql> insert into Insert_Prevent values(0);
ERROR 1644 (45000): You cannot insert record
mysql> insert into Insert_Prevent values(6);
ERROR 1644 (45000): You cannot insert record

If you insert records between 1 and 5, there won’t be any error. It does not prevent insertion of records since as discussed above our trigger is created to insert records between 1 and 5. The query to insert record is as follows:

mysql> insert into Insert_Prevent values(1);
Query OK, 1 row affected (0.20 sec)
mysql> insert into Insert_Prevent values(5);
Query OK, 1 row affected (0.17 sec)
mysql> insert into Insert_Prevent values(2);
Query OK, 1 row affected (0.11 sec)
mysql> insert into Insert_Prevent values(3);
Query OK, 1 row affected (0.23 sec)

Display all records from the table using select statement. The query is as follows:

mysql> select *from Insert_Prevent;

The following is the output:

+------+
| Id   |
+------+
|    1 |
|    5 |
|    2 |
|    3 |
+------+
4 rows in set (0.00 sec)
raja
Published on 26-Feb-2019 12:08:19
Advertisements