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
FOR EACH ROW
IF(NEW.important_value) < (fancy * dancy * calculation) THEN
DECLARE dummy INT;
Your meaningful error message goes here INTO dummy
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.