Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Use a trigger to stop an insert or update in MySQL?
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)
4K+ Views
