- Trending Categories
- Data Structure
- Operating System
- C Programming
- 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?
152 Lectures 16 hours
87 Lectures 5.5 hours
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)
- How to ceate MySQL Trigger before Insert?
- How to use Boto3 to stop a trigger in AWS Glue Data Catalog
- Is it possible to make an insert or an update in the same MySQL query?
- MySQL trigger to insert row into another table?
- How can we create and use a MySQL trigger?
- How to validate documents before insert or update in MongoDB?
- How to multiple insert or batch insert at a time in MySQL query?
- Implement MySQL trigger in the first table to insert records in the second table?
- Update MySQL table on INSERT command with triggers?
- Implement INSERT … ON DUPLICATE KEY UPDATE in MySQL
- Implementing INSERT… ON DUPLICATE KEY UPDATE in MySQL
- Use boolean value to stop a thread in Java
- How to insert an array of values in a MySQL table with a single INSERT?
- Update an entire row in MySQL?
- How to use an update function to animate a NetworkX graph in Matplotlib?