2

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?

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

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; $$
  • +1. But if it's mysql 5.5 and up, it's probably better to use signals. – a1ex07 Feb 29 '12 at 0:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.