Using triggers to stop inserts or updates in MySQL?

You need to use the SIGNAL SQL STATE command to stop inserts or updates 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 to prevent records from being inserted in the table under certain circumstances. The query to create the 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 &#39;45000&#39;
   -> SET MESSAGE_TEXT = &#39;You can not insert record&#39;;
   -> END IF;
   -> END //
Query OK, 0 rows affected (0.20 sec)
mysql> DELIMITER ;

The above trigger will stop inserting whenever a record less than 0 or greater than 5 is inserted.

Now let us create a table first. The query to create the table is as follows:

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

Now insert records less than 0 or greater than 5. This will result in an error message because whenever a record less than 0 or greater than 5 is inserted, a trigger is created to stop the insertion. 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, no error will occur. It does not prevent record insertion because as mentioned above, we create triggers to insert records between 1 and 5. The query to insert records 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)

Use the select statement to display all records in the table. 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)

The above is the detailed content of Using triggers to stop inserts or updates in MySQL?. For more information, please follow other related articles on the PHP Chinese website!