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 Answer 1

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

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .