Use a Trigger to Stop an Insert or Update in MySQL

The MySQL trigger can be used to automatically terminate an INSERT or UPDATE operation if specific conditions are not met. This is achieved by adding logic to the trigger to detect incorrect data or violations of a rule. If the condition is satisfied, the SIGNAL statement is used to show a customized error message and terminate the procedure. Thus, it ensures that the database has valid, clean, and consistent data.

For instance, if a value is being updated or inserted in the column beyond the allowed range, the trigger can stop the action and give a customized error message.

You need to use SIGNAL SQL STATE command to stop an insert or update in MySQL.

MYSQL SIGNAL statement

In MYSQL, the signal statement is used explicitly to raise an error or a warning in stored procedures, triggers, and Events. The statement is used to provide communication of error information or enforcing custom validation or error handling within the database logic.

It allows the customization of error messages by using the SET MESSAGE_TEXT command, which is useful during error message modification to produce more meaningful feedback to handlers, applications, or clients. This approach is useful for implementing custom error messages to improve database integrity and control.

Syntax

Following is the syntax to create a trigger with SIGNAL statement -

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

Let us now understand the important terms used in the above syntax.

• SIGNAL: This command triggers the error manually. It allows you to create a custom error message and stop the current operation (insert or update) if certain conditions are not met.

• SQL STATE 45000: This code specifies the type of error. '45000' is a generic error code for user-defined errors. This code tells the MYSQL the error is custom and not from a built-in MYSQL error.

• SET MESSAGE_TEXT: This sets the custom message that will be displayed to the user if the condition is met. This message provides the details about the error.

Example

Creating a trigger to prevent inserting values into a table if the value of Id is less than 1 or greater than 5.

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

CREATE TABLE Insert_Prevent(
   Id int 
);

Now, create a trigger that would prevent us from inserting a record in the table whenever you insert a record less than 0 or greater than 5. The query to create a trigger is as follows -

DELIMITER //
 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 //
DELIMITER ;

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.

INSERT INTO Insert_Prevent values(0);

Following is the output of the above query ?

ERROR 1644 (45000): You can not insert record

INSERT INTO Insert_Prevent values(6);

Following is the output of the above query ?

ERROR 1644 (45000): You can not insert record

In the above output, we got an error because the trigger got activated when we have inserted values which is less than 1 and greater than 6 and got a customized error because we used signal statement.

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 the record is as follows -

INSERT INTO Insert_Prevent values(1),(5),(2),(3);

Following is the output of the above code ?

Query OK, 4 rows affected (0.23 sec)

Display all records from the table using SELECT statement. The query is as follows -

SELECT * FROM Insert_Prevent;

Following is the output of the above query ?

Conclusion

By using the signal statement in the trigger you can prevent unwanted changes and give the user clear, customized error messages. This prevents from inserting invalid data into the database by blocking an operation which doesn't meet predefined ensuring that only the permissible data is added or updated in the table.