MySQL using trigger instead of check

Question

Is there any way that I could use trigger instead of check? like I have

CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER test BEFORE UPDATE ON apply FOR EACH ROW BEGIN IF NEW.cname = "hi" THEN
SET NEW.cname = "hello"; ELSE
RETURN 1; END IF; END; $$

but return 1 doesn't work. Is there anyway that I can prevent the query from executing?

Answer 1

You do not need a RETURN 1 because Trigger is a Stored Procedure, not a Stored Function.

If you want to break it on purpose, that's acceptable.

I wrote two posts about how to break a trigger midstream

• Trigger in MySQL to prevent insertion (Apr 25, 2011)
• check constraint does not work? (Dec 23, 2011)

Try this:

CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `test`
BEFORE UPDATE ON `apply` 
FOR EACH ROW
BEGIN
    DECLARE dummy INT;

    IF NEW.cname = "hi" THEN          
        SET NEW.cname = "hello";
    ELSE
        SELECT no_such_column INTO dummy
        FROM information_schema.no_such_table;
    END IF;

END; $$