TRIGGERs that cause INSERTs to fail? Possible?

by

From this blog post

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 mytabletriggerexample
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;

More Related Contents:

  1. Throw an error preventing a table update in a MySQL trigger
  2. How to abort INSERT operation in MySql trigger?
  3. MySql Error: Can’t update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger
  4. MySQL Trigger after update only if row has changed
  5. mysql after insert trigger which updates another table’s column
  6. How to program a MySQL trigger to insert row into another table?
  7. Use a trigger to stop an insert or update
  8. MySQL Fire Trigger for both Insert and Update
  9. MySQL trigger On Insert/Update events
  10. Can a MySQL trigger simulate a CHECK constraint? [duplicate]
  11. MySQL error 1436: Thread stack overrun, with simple query
  12. MySQL trigger to update a field to the value of id
  13. How can I set a maximum number of rows in MySQL table?
  14. mysql trigger stored trigger is already used by statement which invoked stored trigger
  15. My MySQL trigger doesn’t work, simple syntax, not complicated
  16. MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  17. MySQL Trigger: Delete From Table AFTER DELETE
  18. Is it Possible to Enforce Data Checking in MySQL using Regular expression
  19. Can I launch a trigger on select statement in mysql?
  20. Should I normalize my DB or not?
  21. Using MySQL triggers to log all table changes to a secondary table
  22. RMySQL dbWriteTable with field.types
  23. MySQL: How do I use delimiters in triggers?
  24. MySQL listen notify equivalent
  25. MySQL disable all triggers
  26. How to store data with dynamic number of attributes in a database
  27. Calling an url from a trigger in mysql
  28. Enable remote MySQL connection: ERROR 1045 (28000): Access denied for user
  29. How to display open transactions in MySQL
  30. MYSQL Select One Random record from each Category

Leave a Comment