How to Prevent Inserts or Updates with Invalid Data Using Triggers in MySQL?

Preventing Inserts or Updates with Triggers in MySQL

When dealing with check constraints, MySQL may sometimes ignore them. This raises the question of how to use triggers effectively to control inserts and updates in such cases. For instance, a table named "foo" has an "agency" attribute that can only accept values between 1 and 5.

Initially, a trigger was created to prevent values outside this range from being inserted into the table:

create trigger agency_check
before insert on foo
for each row
begin
    if (new.agency < 1 or new.agency > 5) then
        #Do nothing?
    end if;
end

However, the trigger doesn't explicitly stop the insertion or update of rows with invalid agency values. To implement such behavior, consider using the SIGNAL syntax:

create trigger agency_check
before insert on foo
for each row
begin
    if (new.agency < 1 or new.agency > 5) then
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'your error message';
    end if
end

This modified trigger will now throw an error with the specified message, ensuring that the insert or update is unsuccessful when agency values fall outside the predefined range. This approach provides a more robust method for enforcing check constraints in MySQL.

The above is the detailed content of How to Prevent Inserts or Updates with Invalid Data Using Triggers in MySQL?. For more information, please follow other related articles on the PHP Chinese website!