MySQL Triggers: How do you abort an INSERT, UPDATE or DELETE with a trigger?

On EfNet’s #mysql someone asked:

How do I make a trigger abort the operation if my business rule fails?

In MySQL 5.0 and 5.1 you need to resort to some trickery to make a trigger fail and deliver a meaningful error message. The MySQL Stored Procedure FAQ says this about error handling:

SP 11. Do SPs have a “raise” statement to “raise application errors”? Sorry, not at present. The SQL standard SIGNAL and RESIGNAL statements are on the TODO.

Perhaps MySQL 5.2 will include SIGNAL statement which will make this hack stolen straight from MySQL Stored Procedure Programming obsolete. What is the hack? You’re going to force MySQL to attempt to use a column that does not exist. Ugly? Yes. Does it work? Sure.

CREATE TRIGGER mytable_trigger_example
BEFORE INSERT
    FOR EACH ROW
BEGIN
    IF(NEW.important_value) < (fancy * dancy * calculation) THEN
        DECLARE dummy INT;
SELECT Your meaningful error message goes here INTO dummy FROM mytable WHERE mytable.id=new.id END IF; END;

Now that you can abort an operation, you should ask yourself if this is really the best place for your business logic. I don’t think it is for most applications. On the other hand, Michael Simon told me at a Seattle MySQL Meetup that triggers might be the best/only place to enforce regulations such as HIPAA. At least give it some thought before you blindly start enforcing business rules with stored procedures and triggers.

3 Responses to “MySQL Triggers: How do you abort an INSERT, UPDATE or DELETE with a trigger?”

  1. Thanks for the assist; coming from PostgreSQL, MySQL triggers and functions seem like a hell of a mess…

    I would say it’s the best place to enforce business rules if you want consistent data. There might be many ways in your application to edit the data in a given table, and you can check things in those many places; or you can enforce it at a database level and then handle that appropriately further up the stack.

    The second way is much more simple and will catch problems even if checks are not followed properly in a piece of application code.

  2. Here’s a much simpler implementation of the hack that doesn’t require all that PSQL cruft:

    drop table your_meaningful_message_goes_here;

    This will throw an exception that looks like this:

    ERROR 1051 (42S02): Unknown table ‘your_meaningful_message_goes_here’

    At least the user will get the message.

  3. [...] are all the same, so i’m thinking about using only one table. I’ve read here and here (even if i don’t understand fully this example) that i can enforce a mysql rule to have [...]

Leave a Reply