How to abort INSERT operation in MySql trigger?

by

I came across this and although the solution works I later came across what feels to me like a better solution. I suspect this wasn’t an option when this question was originally answered.

CREATE TRIGGER `TestTable_SomeTrigger`
BEFORE UPDATE ON `test_table`
FOR EACH ROW
BEGIN
    DECLARE msg VARCHAR(255);
    IF (SomeTestToFail = "FAIL!") THEN
        set msg = "DIE: You broke the rules... I will now Smite you, hold still...";
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
    END IF;

    -- Do any other code here you may want to occur if it's all OK or leave blank it will be
    --  skipped if the above if is true
END$$

This will now return a nice (or evil!) error message that you can trap.
For more info on this see: http://dev.mysql.com/doc/refman/5.5/en/signal.html

I hope this helps someone else!

More Related Contents:

  1. Remove duplicate rows in MySQL
  2. Find duplicate records in MySQL
  3. How to delete duplicates on a MySQL table?
  4. Throw an error preventing a table update in a MySQL trigger
  5. MySQL Trigger after update only if row has changed
  6. How to program a MySQL trigger to insert row into another table?
  7. TRIGGERs that cause INSERTs to fail? Possible?
  8. MySQL Fire Trigger for both Insert and Update
  9. Can a MySQL trigger simulate a CHECK constraint? [duplicate]
  10. MySQL error 1436: Thread stack overrun, with simple query
  11. In MySQL, can I copy one row to insert into the same table?
  12. MySQL trigger to update a field to the value of id
  13. mysql trigger stored trigger is already used by statement which invoked stored trigger
  14. My MySQL trigger doesn’t work, simple syntax, not complicated
  15. MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  16. MySQL Trigger: Delete From Table AFTER DELETE
  17. Removing duplicates with unique index
  18. How to delete duplicate rows from a MySQL table
  19. Is it Possible to Enforce Data Checking in MySQL using Regular expression
  20. How do I delete all the duplicate records in a MySQL table without temp tables
  21. MySQL delete duplicate records but keep latest
  22. How to remove duplicate entries from a mysql db?
  23. Using MySQL triggers to log all table changes to a secondary table
  24. MySQL: How do I use delimiters in triggers?
  25. MySQL listen notify equivalent
  26. Count duplicates records in Mysql table?
  27. MySQL disable all triggers
  28. Calling an url from a trigger in mysql
  29. How to do unique constraint works with NULL value in MySQL
  30. Force MySQL to return duplicates from WHERE IN clause without using JOIN/UNION?

Leave a Comment