MySQL using trigger instead of check

MySQLtrigger

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?

Best Answer

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

Best Answer

Related Question